Code only runs on Excel 2007 if file is opened from within Excel

Question

I have some relatively simple code that loops through multiple files in a folder, opens each and copies a range of data into a new worksheet. I wrote the code using Excel 2013. Some users have 2007 on their box. If the 2007 users open the file from within Excel (i.e File-Open, and navigate to the directory), it runs fine. If the 2007 users open the file from Windows Explorer, all the macros run, except the file name variable "myFile" that I use in the loop is always EMPTY. It's like it won't navigate to the directory. I do not get any errors, it just executes the code and goes right to the end of the loop (which says to stop when "myFile" is empty) -- then continues on with the next macro. With Excel 2013, the program runs fine regardless of how it is opened. I thought my answer might have been contained in this thread: Excel Workbook Open Event macro doesn't always run but the Q&A doesn't seem to apply directly to my issue since the macros actually run. It seems to be just the navigation to the file location that won't work. Here is the snippet of code that runs the loop:

    Dim myfile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim DataBlock As Range

'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

Set wb = Workbooks("ComboFile.xlsm")
Set ws = wb.Sheets("Sheet1")    'change desired sheet

ChDir "r:\BSI\Sys9000"
myfile = Dir("*.xlsx")

Do Until myfile = ""
    Workbooks.Open Filename:=myfile
    Set DataBlock = Range("A2").CurrentRegion
    DataBlock.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
    Windows(myfile).Close
    myfile = Dir
Loop

'Reset Macro Optimization Settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

Any suggestions welcome. Thank you.

Answer 1

I would recommend you replace this:

ChDir "r:\BSI\Sys9000"
myfile = Dir("*.xlsx")

with just this:

myfile = Dir("r:\BSI\Sys9000\*.xlsx")

ChDir won't change drives so if the current directory is not on the R drive, the code would fail.

You will also need to change this:

Workbooks.Open Filename:=myfile

to include the path:

Workbooks.Open Filename:="r:\BSI\Sys9000\" & myfile