Why my specific path Get Folder is not working when using FileSystemObject VBA

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello

This macro is unable to find a specific path folder to allow me to select which files I can choose. It works fine when open but I have to manually to find D: Drive and then to look for the folder and then select which files I can choose.

Could the path drive be too long?

Can you point me where it goes wrong please?

Thank you

Regards

V

VBA Code:
Sub FindFile()

    Dim fso As Object 'FileSystemObject
    Dim fld As Object 'Folder
    Dim fl As Object 'File
    Dim Mask As String
   
    
    Set fso = CreateObject("scripting.FileSystemObject") ' late binding

 
    Set fldStart = fso.GetFolder("D:\WS\WSD_st\OPS_\Data_Processing\imports\MyThankYou")  ' <-- use your FileDialog code here
   
    For Each fld In fldStart.Files
        If InStr(1, fld.Name, "supporters-file*") > 0 Then ' to select which files
            Set fl = fld
            Exit For
        End If
    Next
   
    If fld Is Nothing Then
   
   
      With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
          .AllowMultiSelect = False
 
        'Show the dialog box
        .Show
       
        'Store in fullpath variable
        Set fl = fso.GetFile(.SelectedItems.Item(1))
    End With
    End If

    Set tempbook = Workbooks.Open(fl.Path, Local:=True)
   

End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Try putting a backslash:

\

at the end of your network path. e.g.... "D:\WS\WSD_st\OPS_\Data_Processing\imports\MyThankYou\"
 
Upvote 0
Does your file name really have an asterisk in it?
 
Upvote 0
Does your file name really have an asterisk in it?

Hi

I add a backslash. Unfortunately the result is still the same.

I have removed an asterisk.

In the folder's the files are as see the following

Supporters-file_ 16102022
Supporters-file _16112022
Supporters-file_16082022
Supporters-file _16072022
Supporters-file _16062022

I have an option to choose which file I can select.

V
 
Upvote 0
Your name check is currently case-sensitive. Try:

VBA Code:
If InStr(1, fld.Name, "supporters-file", vbtextcompare) > 0 Then
 
Upvote 0
I run a macro again. A prompt up screen a dialog box showing direct to This PC>Documents instead of D:\. Could it be something to do with default setting?
 
Upvote 0
I got it now and it works but it won't let me to choose which files I want to select.
 
Upvote 0
I run a macro test again and it opens to ThisPC>Documents. it wont takes me to look at the specific D\drive to MyThankYou folder.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top