Solved Dealing with long file paths (1 Viewer)

The Rev

Registered User.
Local time
Today, 16:48
Joined
Jan 15, 2003
Messages
118
I have a script that is assigned to a button that open an Excel template and then some auto filling. I am running into an issue when the file path is too long for Access' VBA. I am trying to work around it using ShortPath. But when I try to open the template, it says it can't find the path. Below is my code snippet

Code:
Set objSFolders = CreateObject("WScript.Shell").SpecialFolders
Path = Application.CurrentProject.Path & "\Templates\"
Set fdg = Application.FileDialog(msoFileDialogFilePicker)

With fdg
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "File Type", "*.xlsx", 1
    .InitialFileName = Path
    If .Show = -1 Then
            For Each strSelectedItem In .SelectedItems
            'Make sure File Path isn't too long because Microsoft is stupid
                    FPLength = 0
                    FPLength = Len(strSelectedItem)
                    If FPLength > 218 Then
                        TempFldr = Dir(Path, vbDirectory)
                        If TempFldr = vbNullString Then
                            VBA.FileSystem.MkDir (Path)
                        End If
                        Set StrShortItem = Objfso.GetFile(strSelectedItem)
                        strSelectedItem = StrShortItem.ShortPath & "\" &StrShortItem.Name
                    Else
                        TempFldr = Dir(Path, vbDirectory)
                        If TempFldr = vbNullString Then
                            VBA.FileSystem.MkDir (Path)
                        End If
                    End If
            Set xl = CreateObject("Excel.Application")
            Set wb = xl.Workbooks.Open(strSelectedItem)
            xl.Visible = True
....

And on. When the file path isn't > 218, it opens fine. When it is > 218, it shows an error at the "set wb " that the script can't find the shortpath folder, however, I can put a MSGBOX instream and it shows the whole file name as the strSelectedItem variable. Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:48
Joined
Oct 29, 2018
Messages
21,358
Hi. I just took a quick glance, but I didn't see where you copied or moved the file from the long path to the shorter path.
 

The Rev

Registered User.
Local time
Today, 16:48
Joined
Jan 15, 2003
Messages
118
Hi. I just took a quick glance, but I didn't see where you copied or moved the file from the long path to the shorter path.
Not copying it at this point. Just opening it for modification from its path. If the file's full path character count exceeds 218, Access can't find it. I am trying to get around the character limit by converting the file's path to 8.3 format (short path option). It works up until the next to last line where it errors that it can't find the path specified.
 

The Rev

Registered User.
Local time
Today, 16:48
Joined
Jan 15, 2003
Messages
118
Ugh. figured it out.

strSelectedItem = StrShortItem.ShortPath & "\" &StrShortItem.Name

gives the full shortpath name and then I added the full long file name at the end with & StrShortItem.Name. So basically I had the file name twice. Dumb me...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:48
Joined
Oct 29, 2018
Messages
21,358
Ugh. figured it out.

strSelectedItem = StrShortItem.ShortPath & "\" &StrShortItem.Name

gives the full shortpath name and then I added the full long file name at the end with & StrShortItem.Name. So basically I had the file name twice. Dumb me...
Glad to hear you got it sorted out. Good luck with your project.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:48
Joined
Sep 21, 2011
Messages
14,047
Why are you using For Each, when multi select is set to False?
 

Isaac

Lifelong Learner
Local time
Today, 13:48
Joined
Mar 14, 2017
Messages
8,738
I just want to say thanks for this thread and for posting your solution, as I LOVE fso but have never used shortpath, even when suffering from vb* problems with long paths. Just learned something great new!
 

Users who are viewing this thread

Top Bottom