
Active Member

July 7, 2020

Hey there!
I'm fairly new to VBA so sorry if this is a stupid question.
I have written a fairly simple Macro which copies a sheet from multiple different workbooks and pastes it into one workbook. This works fine when I run it on my PC but when I transfer over to mac I get the following error:
When I go to look at the files manually I can open them no problem its only when the Macro tries to open them that the error is thrown.
Any Ideas on how to fix it?
My code is as follows:
Sub Add_Bridge_1(fileStr)
Dim wbk1 As Workbook, wbk2 As Workbook, mas As Worksheet, fileLoc As String
fileLoc = Range("B10").Value
'add your own file path
'fileStr = "C:\Users\QQ\Desktop\Test\Order 1.xlsx"
Set wbk1 = ActiveWorkbook
Set mas = wbk1.ActiveSheet
Set wbk2 = Workbooks.Add(fileLoc & fileStr)
If wbk2.Sheets(1).Name = "Export Summary" Then
wbk2.Sheets(1).Delete
End If
'wbk2.Sheets("Bridge 1").Copy After:=Workbooks("WorkbookNameYouCopyCodeInto").Sheets(1)
wbk2.Sheets(1).Copy After:=wbk1.Sheets(2)
wbk2.Saved = True
wbk2.Close
wbk1.Activate
mas.Activate
End Sub
Sub RepeatAdd()
Dim fileNames() As Variant
Dim fileLoc As String
Dim wbk1 As Workbook
Dim mas As Worksheet
Set wbk1 = ActiveWorkbook
Set mas = wbk1.ActiveSheet
fileLoc = Range("B10").Value
fileNames = GetFiles(fileLoc)
mas.Activate
For Each fileStr In fileNames
Add_Bridge_1 (fileStr)
Next fileStr
Sheets.Add After:=wbk1.Sheets(2)
ActiveSheet.Name = "START"
Sheets.Add After:=wbk1.Sheets(wbk1.Sheets.Count)
ActiveSheet.Name = "END"
mas.Activate
End Sub
Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile
GetFileNames = Result
End Function
Function GetFiles(ByVal FolderPath As String) As Variant
Dim Books As Variant
Dim FileName As String
Dim Files As Variant
Dim Folder As Variant
Dim n As Long
ReDim Books(1 To 1)
ReDim Files(1 To 1)
n = 1
'Folder = "C:\Users\QQ\Desktop\POLARTEST\Hockey\"
' Filter for .xls, .xlsb, .xlsx, .xlsm, etc.
FileName = Dir(FolderPath & "*.xls*")
While FileName <> ""
Books(n) = Folder
Files(n) = FileName
FileName = Dir()
n = n + 1
If FileName <> "" Then
ReDim Preserve Books(1 To n)
ReDim Preserve Files(1 To n)
End If
Wend
GetFiles = Files
End Function


October 5, 2010

Hi Daniel,
That code works fine for me on my W10 PC.
At what line does it debug on the Mac? Have you stepped through the code to see where it errors?
https://www.myonlinetraininghu.....g-vba-code
https://www.myonlinetraininghu.....ugging-vba
I don't have a Mac so I'd guess that the path in Range("B10").Valuedoesn't exist on the Mac?
Regards
Phil


Trusted Members
Moderators

November 1, 2018

1 Guest(s)
