Excel VBA code to extract information from other workbooks

Question

The code is looking to go to a destination folder which is stated in the dashboard sheet (for example C:\DOCUMENTS) of the main workbook and open each .xlsx or .xls files (using wildcard *.xls*) in the folder and take cells A1:C1 (this is fixed) from each "Sheet 1" and paste them into a named range back in the dashboard of the main work book.  This should repeat on a loop until all workbooks have been opened, copied, pasted and closed again. 

I am finding that the below code does not do this as it breaks down on the line 'myFile = Dir([Location] & "" & myExtension)' and i can not figure out why. 

Any pointers or suggestions are useful.

Sub Macro2()

Dim i As Integer

Dim myFile, myExtension As String

Dim wb, Masterwb As Workbook

Dim xlApp As Excel.Application

Set xlApp = Excel.Application

xlApp.ScreenUpdating = False

xlApp.DisplayAlerts = False

'Target File Extension (must include wildcard "*")

  myExtension = "*.xlsx"

'Target Path with Ending Extention

  myFile = Dir([Location] & "" & myExtension)

i = 0

'Loop through each Excel file in folder

Do While myFile <> ""

    'Set variable equal to opened workbook

    Set wb = xlApp.Workbooks.Open(myFile, , True)

    i = i + 1

    'Set Master Workbook

    Set Masterwb = ThisWorkbook

    'Set Copy From/To Ranges

    SheetCopyFrom = wb.Sheets("Sheet1").Name

    SheetCopyTo = Masterwb.Sheets("Sheet2").Name

    'Copy from Source to Target

    Masterwb.Sheets(SheetCopyTo).[rng_CopyStart].Offset(i, 0).Value = wb.Sheets(SheetCopyFrom).Range("A1:C1").Value

    'Close Open Workbook wo/Save

    wb.Close False

    'Get next file name

    myFile = Dir

Loop

ResetSettings:

'Reset Macro Optimization Settings

  xlApp.EnableEvents = True

  xlApp.DisplayAlerts = True

  xlApp.Calculation = xlCalculationAutomatic

  xlApp.ScreenUpdating = True

End Sub

Answer 1

You have not said if the code returns an error or you simply do not get a file name returned to the variable myFile.

If the problem is just not returning a value to myFile then the path at Location might be invalid.

If you are getting an error then because Location is a defined name then I suggest that there might a problem with the defined name or the contents of the cell.

On the worksheet, select Name manager and ensure that there is not an error in the defining of the Named range like #Ref in the Refers to field.

If previous step is OK then check that you have a valid path in the cell.

If still looks OK then insert a line like the following to print the string attached to the Dir command to the Immediate window and you will probably see what the error is. (the line of code needs to be immediately prior to the Dir line like the following.

'Target Path with Ending Extention

   Debug.Print [Location] & "" & myExtension        'Insert this line

   myFile = Dir([Location] & "" & myExtension)

Just in case you are not familiar with the Immediate window, in the VBA editor, open if with Ctrl and G.