Opening other fiels in same folder

**KevinThomas** · 03-18-2010, 06:47 AM

Hello, I have a master sheet (Called daily report) which has a macro to call other excel sheets to open within the same folder.

The folder is on a shared directory (P for me) and the problem i have is that the letter of the directory is different on other people's computers.

Is there a better way of coding the below so the macro will work without having to state the folder directory letter?

By the way, this is just the beginning of the macro - the remainder is irrevlevant to my request.

Code:

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
        "P:\Schedules\so.xlsx"
Workbooks.Open Filename:= _
        "P:\Schedules\mrp.xlsx"
Workbooks.Open Filename:= _
        "P:\Schedules\orders.xlsx"
Workbooks.Open Filename:= _
        "P:\Schedules\otd.xlsx"
Workbooks.Open Filename:= _
        "P:\Schedules\financial.xlsx"
Workbooks.Open Filename:= _
        "P:\Schedules\inventory.xlsx"
Windows("Daily Report.xlsx").Activate

**davegugg** · 03-18-2010, 01:17 PM

There are a couple of things I can think of. The easiest and most reliable would be to use an .inputbox to have a user type in the drive letter or an application.getopenfilename to select the first file, then get the drive and folder name from that.

If that is not an option, the below code will go through each drive A:\ to Z:\ and search for the first file you list in your code above. Once found, it will make the variable strFound equal to that DRIVE (ex: P:\)

Code:

Public Sub FindDirectory()

Dim strDrive As String
Dim strPath As String
Dim strFound As String

For i = 1 To 26
    strDrive = Chr(i + 64) & ":/"
    strPath = strDrive & "Schedules/so.xlsx"
    If Dir(strPath) > "" Then
        strFound = strDrive
    End If
Next i

End Sub

After putting this code first, you'd have to alter the rest of your code to use strFound:

Code:

Workbooks.Open Filename:= _
        strFound & "Schedules\so.xlsx"
Workbooks.Open Filename:= _
        strFound & "Schedules\mrp.xlsx"
...

Thread: Opening other fiels in same folder

Thread Tools

Display

Opening other fiels in same folder

Re: Opening other fiels in same folder

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions