Importing Data from Multiple Excel Documents Into a Single File MAC Issues

Dear All

Struggling to import data from multiple excel documents into a single file.

I found some helpful threads and codes but am struggling to implement them. I am pretty new to visual basic or you can say a novice.

I found these useful codes below from this thread: . I decided to start a new thread instead of jumping on the ones I read as I am told this is good practice. The first one hangs on the fourth line:

CurrentFile = Dir(strFldrPath & ".xls")

I think there might be some compatibility issues with directory structures etc. as I am using Excel 2011 for mac. I've used various file extensions xls, xlsx, xlsm but don't seem to make any difference. Can anyone suggest a quick fix to get this working?!

Many thanks,


Here's the code:

Sub MacroImportWIA4()
    
    Dim CellList As String: CellList = "A2,A5:05,A8:I8,A11:K11,A15:P15,A18:F18"
    Const strFldrPath As String = "Macintosh HD:Users:oldmac:Desktop:...:"
    Application.ScreenUpdating = False
    Dim CurrentFile As String: CurrentFile = Dir(strFldrPath & ".xls")
    Dim wsDest As Worksheet:    Set wsDest = ActiveWorkbook.ActiveSheet
    Dim wb As Workbook, rngNextLine As Range
    While CurrentFile <> vbNullString
        Set wb = Workbooks.Open(Filename:=strFldrPath & CurrentFile)
        Set rngNextLine = wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        rngNextLine.Resize(1, wb.Sheets(1).Range(CellList).Cells.Count).Value = wb.Sheets(1).Range(CellList).Value
        wb.Close False
        CurrentFile = Dir
    Wend
    Application.ScreenUpdating = True
    
End Sub 


The other one that I found hangs on :    FN = Dir(MyDir & "*.xls")

Here's the code in full:

Sub MacroImportWIA3()
  Dim C As Long
  Dim Cell As Range
  Dim DstRng As Range
  Dim MyDir, FN As String
  Dim LR As Long
  Dim SrcRng As Range
  
    Application.ScreenUpdating = False
       
   'Runtime
    MyDir = "Macintosh HD:Users:oldmac:Desktop:...:"
    
    Set DstRng = ThisWorkbook.Sheets("Sheet1").Range("A1")
    Set DstRng = DstRng.Resize(RowSize:=DstRng.Parent.UsedRange.Rows.Count)
    
     FN = Dir(MyDir & "*.xls")
    
      Do While FN <> ""
        If FN <> ThisWorkbook.Name Then
            With Workbooks.Open(MyDir & FN)
                Set SrcRng = .Sheets("Sheet1").Range("A2,A5:05,A8:I8,A11:K11,A15:P15,A18:F18")
                  For Each Cell In SrcRng
                    Cell.Copy DstRng.Offset(LR, C)
                    C = C + 1
                  Next Cell
                .Close False
                C = 0
                LR = LR + 1
            End With
        End If
        FN = Dir()
      Loop
      
    Application.ScreenUpdating = True
    
End Sub

Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.