Change file path in VBA

[ceytl]

Hello,

If I take the code below and try to run it on another computer I will have to change the file path.

How would I change the following lines of code so it points to the same folder no matter what computer I am using?

All the computers have the same folder on the desktop, called "orders"

MyFiles = Dir("c:\users\ucal\desktop\orders\*.xlsx")

Workbooks.Open "c:\users\ucal\desktop\orders\" & MyFiles

Sub OpenClose()
'Step 1:Declare your variables
    Dim MyFiles As String
'Step 2: Specify a target folder/directory, you may change it.
    MyFiles = Dir("c:\users\ucal\desktop\orders\*.xlsx")
    Do While MyFiles <> ""
'Step 3: Open Workbooks one by one
    Workbooks.Open "c:\users\ucal\desktop\orders\" & MyFiles

    'run some code here
    Split

    ActiveWorkbook.Close SaveChanges:=True

'Step 4: Next File in the folder/Directory
    MyFiles = Dir
    Loop
End Sub

Thanks!

 

[Marc L]

Hello, a VBA demonstration for starters :​

Sub Demo1()
    Dim P$, F$
        P = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\orders\"
        F = Dir$(P & "*.xlsx"):  If F = "" Then Beep: Exit Sub
        Application.ScreenUpdating = False
    Do
        Workbooks.Open P & F
          
        ActiveWorkbook.Close True
        F = Dir$
    Loop Until F = ""
        Application.ScreenUpdating = True
End Sub