Runtime 1004 error on existing file

Pobek

Board Regular
Joined
Jul 7, 2015
Messages
89
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2015
Messages
89
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
And what is the file exact file name that is not working out?
 

Pobek

Board Regular
Joined
Jul 7, 2015
Messages
89

ADVERTISEMENT

"Underwritting MasterDB" is the filename.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2015
Messages
89

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I made a few minor changes to your code.
Let me know what the message box returns when your have an error.
Code:
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

Board Regular
Joined
Jul 7, 2015
Messages
89
I made a few minor changes to your code.
Let me know what the message box returns when your have an error.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
OK, try changing the message box line to this now:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,902
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top