0

I am trying to create a macro that will allow me to import data from all files ending in ".xlsx" in a specified folder into a single spreadsheet. I've mostly figured it out, but there's a recurring issue that requires a manual work-around until I figure out a solution.

The source .xlsx files contain a standard template for inputting data and are populated and submitted to me by other users. I download these files into a specified folder on my computer. However, the Dir function does not recognize any of the .xlsx files in the specified folder unless I open the file in Excel and click "Save". Once I do that, the Dir function works perfectly and all the .xlsx files are listed in the Immediate window. I'd appreciate any insight anyone has regarding why this is happening and if there's any way to fix the code so it recognizes the files without opening and saving.

Sub Test()
Dim strPath As String
Dim strFile As String

strPath = ActiveWorkbook.Path & Application.PathSeparator & "ReferenceFolderName" & Application.PathSeparator
strFile = Dir(strPath, MacID("XLSX"))

Do While Len(strFile) > 0
    strFile = Dir
    Debug.Print strFile
Loop
End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
bdn711
  • 27
  • 5
  • I don't know what the issue is but wanted to point out that your debug will skip the first file (as you call Dir again before printing it) Maybe try Ron's code here: http://www.rondebruin.nl/mac/mac013.htm – Tim Williams Jan 10 '17 at 02:16
  • 1
    Seems to be a known problem that if the file is not created on a Mac then the MacID will be empty until the file has been saved. Maybe try looping without any filter and just check filename extension to filter out the Excel files – Tim Williams Jan 10 '17 at 02:21

0 Answers0