Change file path in VBA

ceytl

Board Regular
Joined
Jun 6, 2009
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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

Rich (BB code):
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hello, a VBA demonstration for starters :​
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top