Solved Retrieve full file path of selected file (1 Viewer)

The Rev

Registered User.
Local time
Today, 06:33
Joined
Jan 15, 2003
Messages
118
Good day! I have a button coded in my database to select an Excel file for import into my DB. We have some particular requirements for folder and file names, and we are running into file path length issues. If the file path is over 255 characters, the file errors on import. I'm trying to come up with a way to get the file's full path and full file name, count the length, and if over 255 characters, move the file to a folder that won't have the issue before importing it into the DB. I can handle the moving file part, but I'm stuck on getting the full file name and path to count it. Any help would be appreciated. Here's the top part of my code where I'm stuck.
Code:
Const msoFileDialogFilePicker As Long = 3
Dim strSelectedFile As String
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim FPLength As Integer
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objSFolders = CreateObject("WScript.Shell").SpecialFolders
Path = objSFolders("MyDocuments")

With fdg
  .AllowMultiSelect = True
  .Filters.Clear
  .Filters.Add "File Type", "*.xlsx", 1
  .InitialFileName = Path
    If .Show = -1 Then
    For Each strselecteditem In .SelectedItems
    FPLength = 0
    FPLength = Len(

It goes on to do some Excel formatting things before importing into a table. That's the pertinent part to my issue currently.

Thanks again!
 

June7

AWF VIP
Local time
Today, 02:33
Joined
Mar 9, 2014
Messages
5,425
You declare strSelectedFile but use strselecteditem.

Try:
FPLength = Len(strselecteditem)
 

The Rev

Registered User.
Local time
Today, 06:33
Joined
Jan 15, 2003
Messages
118
You declare strSelectedFile but use strselecteditem.

Try:
FPLength = Len(strselecteditem)
Well poo. I sure missed that one!

I'll try the variables length and let you know.
 

June7

AWF VIP
Local time
Today, 02:33
Joined
Mar 9, 2014
Messages
5,425
Do you have Option Explicit in each module header?
 

The Rev

Registered User.
Local time
Today, 06:33
Joined
Jan 15, 2003
Messages
118
Do you have Option Explicit in each module header?
Indeed I do. And the variable worked just like you said. I hate when something is that simple and I just missed it. Thanks for the sanity check
 

Users who are viewing this thread

Top Bottom