Runtime 1004 error on existing file

[Pobek]

I am getting this error when I point my macro to a file in a specific directory. Problem is THE FILE IS THERE!!! There are some actions that I have taken:

1. I made copies of the same file with varying length in names.

2. The file with the shorter names are found whilst sometimes the files with the longer names have part of the end cut of (i.e. a runtime error when a filename such as "September-DB" is being looked for under "Septembe" .... cutting off the "r-DB". HOW ON EARTH IS THIS THE CASE!????

3. Copied all the input files in their input folder to a shorter directory (a Path not so long) and for this shorter path, the macro finds all files (including the ones with longer names).

It seems that there is something that is making my code cut of the end of some of my file names particularly if they are sitting in a long and deep file-path ... could there be a limit to the length programmed somewhere? I doubt this as its not the 1st time I have developed models that look for long file names in deep and long file-paths ... HELP!!

I have a 2013 excel.

 

[Joe4]

Strings are limited to 255 characters, and I am guessing that you are exceeding that limit.
If that doesn't answer your question, please post your code and then full path and name of your file.

 

[Pobek]

The path is:

C:\Users\POBEK\Documents\Professional\POBEK\POBEK Consulting\Buss\Clients\Linkage Assurance\Internal Model Dev\Reins&Claims\Reins\Commission Structure\Reinsurance and Claims\Reinsurance\Inputs\Underwriting

file name
Underwritting MasterDB



Code:

Sub openfile()

'On Error GoTo eh

Dim Opath As String
Dim ifile As String
Dim TWB As Workbook
Dim wkbk As Workbook
Dim save_file As String

Set TWB = ThisWorkbook



Opath = Range("CURRDBTPATH").Value

ifile = Cells(11, 5).Value
Opfile = Opath & "" & ifile & ".xlsb"


Set wkbk = Workbooks.Open(Opfile)
Exit Sub
eh: MsgBox ("Error in Code")
End Sub

 

[Joe4]

And what is the file exact file name that is not working out?

 

[Pobek]

"Underwritting MasterDB" is the filename.

 

[Joe4]

I thought you said that you had longer names with months in them?

2. The file with the shorter names are found whilst sometimes the files with the longer names have part of the end cut of (i.e. a runtime error when a filename such as "September-DB" is being looked for under "Septembe" .... cutting off the "r-DB". HOW ON EARTH IS THIS THE CASE!????

 

[Pobek]

I thought you said that you had longer names with months in them?

Hi (Thanks for the assistance by the way)

Yeah the word "Underwritting MasterDB" is the longer version of the name. I saved other files that had names "UDB" "Master" "MDB" and so on ...

 

[Joe4]

I made a few minor changes to your code.
Let me know what the message box returns when your have an error.

Sub MyOpenFile()

'On Error GoTo eh

Dim Opath As String
Dim Opfile As String
Dim ifile As String
Dim TWB As Workbook
Dim wkbk As Workbook
Dim save_file As String

Set TWB = ThisWorkbook

Opath = Range("CURRDBTPATH").Value

ifile = Cells(11, 5).Value
Opfile = Opath & "\" & ifile & ".xlsb"

Set wkbk = Workbooks.Open(Opfile)
Exit Sub

eh:
    MsgBox "Error: Length is: " & Len(Opath & "\" & ifile & ".xlsb")

End Sub

 

[Pobek]

I made a few minor changes to your code.
Let me know what the message box returns when your have an error.

Sub MyOpenFile()

'On Error GoTo eh

Dim Opath As String
Dim Opfile As String
Dim ifile As String
Dim TWB As Workbook
Dim wkbk As Workbook
Dim save_file As String

Set TWB = ThisWorkbook

Opath = Range("CURRDBTPATH").Value

ifile = Cells(11, 5).Value
Opfile = Opath & "\" & ifile & ".xlsb"

Set wkbk = Workbooks.Open(Opfile)
Exit Sub

eh:
    MsgBox "Error: Length is: " & Len(Opath & "\" & ifile & ".xlsb")

End Sub

Hi ..

It says

Error: length is 233

 

[Joe4]

OK, try changing the message box line to this now:

MsgBox Opfile

Then, when you get an error, check to make sure that you actually have a file by that exact name that currently exists in that exact file path.
I suspect that there may be an error in there somewhere.