Solved Dealing with long file paths

[The Rev]

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

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]

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]

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]

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]

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]

Why are you using For Each, when multi select is set to False?

 

[Isaac]

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!