VBA: Run-time Error 1004 - path too long

[pennep]

Hi,

I was using Excel 2010 until yesterday and upgraded to Excel 2016 today. I came across this problem and can't find a way around. I wonder if anyone could help me with this.

The macro I work with reads a path of a file I want to work on. Path to a files are usually too long (eg 243 char long) with almost 20 spaces in them.

Basically, what I found out is that excel 2010 I used to work on read a space as 1 character (" "), where as Excel 2016 reads a space as "%20" i.e. 3 characters. As a result, the long path is now 286 characters long.

VBA can only hold up to 256 characters in the file path. Hence, I get Run-time Error 1004 - path too long.

I use this code to get file path:

vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & "*.xl*", 1, "Select Excel File", "Open", False)

Now, my question is, is there any way for Excel 2016 to read a space in the file path as " " instead of "%20" OR any other way without having to move the files in shorter path?

Thank you.

 

[Norie]

Is it Application.GetOpenFilename that's returning the problem path?

If it is, and the path includes %20s, have you tried replacing them with single spaces?

vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & "*.xl*", 1, "Select Excel File", "Open", False)

vFile = Replace(vFile, "%20", " ")

' continue with code to open file

 

[pennep]

Yeah, still not working

 

[pennep]

Yeah, I forgot to mention before.. I tried replacing "%20" by " " and it the problem still exists.

As you said, before I could use the replace function, Application.GetOpenfilename is returning the problem.

Is it Application.GetOpenFilename that's returning the problem path?

If it is, and the path includes %20s, have you tried replacing them with single spaces?

vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & "*.xl*", 1, "Select Excel File", "Open", False)

vFile = Replace(vFile, "%20", " ")

' continue with code to open file