Macro with actions on variable/dynamic pathnames

[Snoopy]

Ian - or whome it might interest
I try to make the macro (below) a bit more flexible to adopt dynamic
folders
by using a formula in my workbook "UserList.xls" - where - range
A1.value contains thie dynamic filpath as expression - exmpl: "c:
\Aplications\Report\" - which more easily can be used to
direct the macro to take actions and search for files in various
folders - as C:\variable\variable\"

I cant find out how to change the hardcoded expression:
"c:\Aplications\Report\" with formula-based expression:"C:\variable
\variable\"

Best Regards
Snoopy


Former input from Ian
Sub test()
Dim MyFile
' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
MyFile = Dir("c:\Aplications\Report\user\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found
Call aaa(MyFile) ' call your macro
Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in
the
' same directory.
MyFile = Dir
If MyFile = "" Then End ' End subroutine if no further *.xls files
found
Call aaa(MyFile) ' call your macro
Loop
End Sub


Sub aaa(MyFile)
Application.StatusBar = "Collecting data from " & MyFile
Dim i As Integer
Dim MyFiles(1)
Dim MyPasswords(1)
MyFiles(1) = "c:\Aplications\Report\ReportA.xls"
MyPasswords(1) = ""
Workbooks.Open Filename:="c:\Aplications\Report\user\" & MyFile, _
ReadOnly:=True, Password:=MyPasswords(1), UpdateLinks:=0
'the rest of your macro goes here
End sub

 

[Bob Phillips]

Sub test()
Dim MyFile As String
Dim myPath As String
' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
myPath = "c:\Aplications\Report\"
MyFile = Dir(myPath & "user\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found
Call aaa(MyFile) ' call your macro
Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in
the
' same directory.
MyFile = Dir
If MyFile = "" Then End ' End subroutine if no further *.xls files
found
Call aaa(MyFile) ' call your macro
Loop
End Sub