Slow Access to smb Files and Directories from MS office - Excel VBA

Question

I am running MS Office 2013, and I'm trying to list files in an smb directory from Excel VBA code.

unix_path = "\\smb" & unix_path

 ListBox3.Clear
 Dim fil As file
 On Error Resume Next
 If Dir(unix_path, vbDirectory) <> "" Then

 Set MyObject = New Scripting.FileSystemObject
 Set mysource = MyObject.GetFolder(unix_path)
 For Each myFile In mysource.Files
 If InStr(myFile.Name, ".xlsx") > 0 Then
    UserForm1.ListBox3.AddItem myFile.Name
End If

This takes about 15 seconds. The directory itself has only 5 files in it. It is worth mentioning that accessing the directory directly from explorer is much faster (less than 1 second).

Answer 1

FSO has a lot of overhead from my experience and I have experienced some odd issues over our network with it. I do not frequently use it unless I am doing something more specific than this situation.

Please test the following code and see if it still hangs,

unix_path = "\\smb" & unix_path

 ListBox3.Clear
 Dim fil As file
 On Error Resume Next
 If Dir(unix_path, vbDirectory) <> "" Then

 mySource = Dir(unix_path & "*.xlsx")
 Do until mySource = ""
    UserForm1.ListBox3.AddItem mySource
    mySource = Dir()
 loop
 end if