Link to home
Start Free TrialLog in
Avatar of Richard Turner
Richard Turner

asked on

VBA Path Not Found

I want to look in a folder and pull a list of all the files in that folder.
I found a snippet of code that does this and it sometimes works and sometimes not.
I think the issue may be because I need the path to be relative.
Here's the bit of relevant code.  I get a 'path not found' runtime error on the last line.
The path in StrLoc is "..\Analysis & Testing\Analysis Reports\"

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object

StrLoc = "..\" & Right(StrLoc, n) & "\Analysis Reports\"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(StrLoc)

Help would be appreciated.  I cannot use a fixed path string as it changes depending on the user.
But I can say that the Excel file this is in is inside Analysis & Testing and so Analysis Reports is therefore a subfolder and this structure will always be the same.
Avatar of Bill Prew
Bill Prew

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Using Environ$, you can create a complete path to the folder without knowing the user name. Something like:
DocPath = Environ$("USERPROFILE") & "\Documents"
StrLoc = DocPath & "\Analysis Reports\"

Open in new window

Avatar of Richard Turner
Richard Turner


Oh yeah.
I didn't think of that.  
So I guess I just grab the activeworkbook path on the fly and stick the subfolder name on the end.
Thanks for the suggestion.  I'll just check that works for our needs...