Excel VBA script not running on Excel 2016 with FileSystemObject

Question

I've never used VB(A) before, so forgive me if this is a trivial question.

I am trying to run the code outlined here on Excel 2016 on a Mac.

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("/...filepath")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

However, I get the error, that goes off without specifying a line:

Any thoughts on how I can modify this code for Mac?

What line is showing the error? And VBA, not VB Script...Correct? — Rdster, Nov 22 '16 at 21:28
A compile error *should* (IDK about Excel 2016) highlight the offending object. Does it highlight anything in the VBE? — David Zemens, Nov 22 '16 at 21:40
Yes, if you press "OK" it will highlight the first line, but before you press OK, do you see anything else highlighted? For example it might look like this (but with your code instead of my example): http://imgur.com/0wuI6lK — David Zemens, Nov 22 '16 at 21:46
To avoid the 429 error you'd need to revise and use `Dir` function instead of `Scripting.FileSystemObject`, this is arguably faster & more efficient, as well. Example: http://stackoverflow.com/questions/4677881/is-there-an-alternative-to-scripting-filesystemobject-in-excel-2011-vba-for-the — David Zemens, Nov 22 '16 at 21:56

score 1 · Answer 1 · edited May 23 '17 at 12:33

Try this, based on the other (unaccepted) answer to similar question.

Is there an alternative to Scripting.FileSystemObject in Excel 2011 VBA for the mac?

The problem is that Scripting.Runtime library is not available on Mac OS, so you can't do CreateObject("Scripting.FileSystemObject"). This uses the VBA Dir function to build a Collection of files.

Also revised for more efficient "copy" that doesn't use the Copy method.

(untested, so bear with me in case of typos/etc.)

Sub simpleXlsMerger()
Dim bookList As Workbook, vals as Variant
Dim filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set filesObj = GetFileList("/...filepath")
For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    'change "A2" with cell reference of start point for every files here
    'for example "B3:IV" to merge all files start from columns B and rows 3
    'If you're files using more than IV column, change it to the latest column
    'Also change "A" column on "A65536" to the same column as start point
    vals = Range("A2:IV" & Range("A" & Rows.Count).End(xlUp).Row).Value2

    With ThisWorkbook.Worksheets(1)
    'Do not change the following column. It's not the same column as above
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(UBound(vals, 1), UBound(vals, 2)).Value = vals
    End With

    bookList.Close
Next
End Sub

Function GetFileList(folderPath As String) As Collection
'mac vba does not support wildcards in DIR function

    Dim file As String
    Dim returnCollection As New Collection

    If Right$(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If

    file = Dir$(folderPath) 'setup initial file

    Do While Len(file)
        returnCollection.Add folderPath & file
        file = Dir$
    Loop

    Set GetFileList = returnCollection
End Function

score -3 · Answer 2 · edited May 23 '17 at 11:53

-3

In the VBA window, click tools then add reference. Make sure Microsoft Office Object Library (14 or 16) is checked. You will need this for the automation.

An alternative idea would be to create a loop and save each excel file as a csv then run a batch script.

Example: copy *.csv merged.csv

For the CSV loop use: Converting XLS/XLSX files in a folder to CSV

edited May 23 '17 at 11:53

Community

1
1

answered Nov 22 '16 at 21:35

thinkBig

1
1

Nope, reference is not needed when using CreateObject with late binding. – A.S.H Nov 22 '16 at 21:56

Excel VBA script not running on Excel 2016 with FileSystemObject

2 Answers2

In the VBA window, click tools then add reference. Make sure Microsoft Office Object Library (14 or 16) is checked. You will need this for the automation.