用通配符打开文件的代码可以在Windows上运行，但不能在Mac系统上运行

Question

当我在Windows计算机上运行它时，此代码正常运行。在Mac上，我得到:

运行时错误:'1004'对不起，我们找不到文件。xlsx。它是否可能被移动、重命名或删除?

我确认文件'file.xlsx'可用。有没有办法修改我的代码在Windows和Mac上工作?

Sub CollectXLSXData()
' To loop through all .xslx files in a system specified folder and perform a set task on them
Application.ScreenUpdating = False
Dim MergeFileNames As String
Dim MergeFile As Workbook
Dim ws As Worksheet
Dim SourceRange As Range
Dim Data As Worksheet
Set Data= ThisWorkbook.Worksheets("Data")
ChDir ThisWorkbook.Path
MergeFileNames = Dir("*.xlsx")
Do Until MergeFileNames = ""
Set MergeFile = Workbooks.Open(MergeFileNames)
For Each ws In MergeFile.Worksheets
Set SourceRange = ws.Range("A1").CurrentRegion
SourceRange.Offset(1, 0).Resize(SourceRange.Rows.Count - 1).Copy _
Data.Cells(MTIData.Rows.Count, 1).End(xlUp).Offset(1, 0)
Next ws
Application.CutCopyMode = False
MergeFile.Close
MergeFileNames = Dir
Loop
Application.ScreenUpdating = True
End Sub

Answer 1

为了不必处理与MAC系统不兼容的通配符，我继续并引用代码从保存在特定文件夹中的所有文件中获取数据。该文件夹支持任何excel文件格式。现在我的代码可以在Windows和MAC环境下运行，没有任何问题。

Sub CollectFiles()
' To loop through all files in a system specified folder and copy to a master file/sheet
Dim FolderPath As String
Dim Files As String
Dim MergeFile As Workbook
Dim ws As Worksheet
Dim SourceRange As Range
Dim Data As Worksheet
Set Data = ThisWorkbook.Worksheets("Data")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FolderPath = ThisWorkbook.Path & "/xFiles/"
Files = Dir(FolderPath)
Do Until Files = ""
Set MergeFile = Workbooks.Open(FolderPath & Files)
For Each ws In MergeFile.Worksheets

Set SourceRange = ws.Range("A1").CurrentRegion
SourceRange.Offset(1, 0).Resize(SourceRange.Rows.Count - 1).Copy _
Data.Cells(Data.Rows.Count, 1).End(xlUp).Offset(1, 0)
Next ws

Application.CutCopyMode = False
MergeFile.Close
Files = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub