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.
* Excel TableVBA

Avatar of undefined
Last Comment
Richard Turner

8/22/2022 - Mon
Bill Prew

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Korchok

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

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...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes