Dir function not working

[joshcas]

Today I’m working to adapt an old code that I had into a project that I’m working on but for some reason the dir function is not working because it’s returning only blank , I know that there is files in that folder because I can see them in Windows Explorer and when I run the dir command in cmd it would also return the file names in there . Any idea of what I’m doing wrong ?


Sub dev_1()

mydir = "\\VPN Address\EG_AMS_CUBE_FS\DEV\ISS_Templates"
StrFile = Dir(mydir)

MsgBox (StrFile)
End Sub

[Kenneth Hobs]

When you leave the 2nd parameter off, you are telling it to use the default, look for a file. Besides that, you are using a UNC path. If you need UNC, use FSO.

e.g.

MsgBox CreateObject("Scripting.FileSystemObject").FolderExists("\\VPN Address\EG_AMS_CUBE_FS\DEV\ISS_Templates")

[joshcas]

Hi Kenneth ,

Thanks for answering

I'm not sure that I'm following you, inside the ISS_Templates folder there is a bunch of excel files so the default option should be able to see those but I’m getting just blank , cmd (dir) is able to see those files

[Kenneth Hobs]

Error 52 occurs if the UNC path does not exist using UNC and DIR(). If it does exist, "." is returned. It is just to flaky for my taste for UNC paths. When using UNC in DIR() for a vbDirectory, be sure to use a trailing backslash. I guess you can use On Error if you really want to stick with Dir() and UNC paths.


MsgBox Dir("\\matpc37\Excel\",vbDirectory)

I can only give advice, it is up to you to decide if it has merit.

You lost me on the file and cmd (dir). Do you mean the Shell() cmd and dir? That is not the same as VBA's Dir().

If your goal is to get file names in a UNC folder, that is another matter. One can use fso methods for that or the command shell's Dir method. For the latter:

MsgBox CreateObject("Wscript.Shell").Exec("cmd /c dir \\matpc37\excel\*.* /b").StdOut.ReadAll

[snb]

If you need to 'see' Excel files you have to give the method the ability to detect those:


Sub M_snb()
msgbox Dir("\\VPN Address\EG_AMS_CUBE_FS\DEV\ISS_Templates\*.xls")
End Sub

[joshcas]

Kenneth,SNB thank you so much for helping me , I really appreciate it

You are correct Kenneth my goal is to get the file names in a UNC folder , one by one
I’ve been running some tests are here are my findings ….



Sub dev_1()
mydir = ("\\VPN Address\EG_AMS_CUBE_FS\DEV\ISS_Templates\")
StrFile = Dir(mydir)
MsgBox (StrFile)
End Sub

Sub dev_2()
sRemoteFilePath = Worksheets("Connections").Range("CIGShareLink").Value
StrFile = Dir(sRemoteFilePath)
MsgBox (StrFile)
End Sub


dev_1 works fine but I'll need to go into the code to change the address whenever they change the path

dev_2 is dynamic but is really weird because it returns blank, no “.” or anything else , just blank , however the range("CIGShareLink") = \\VPN Address\EG_AMS_CUBE_FS\DEV\ISS_Templates\

My last question will be how can I fix dev_2 or how can I accomplish the same using fso ?

[joshcas]

I’ve figured out that on the range you have to remove the hyperlink because otherwise it will return blank, dev_2 is working now

[snb]

Applied to KH's suggestion:


MsgBox CreateObject("Wscript.Shell").Exec("cmd /c dir """ & sheets("Connections").Range("CIGShareLink").Value & "*.xls"" /b").StdOut.ReadAll

[joshcas]

Thank you so much for the alternative methods, I appreciate it