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

Question

The answers to How can I install/use “Scripting.FileSystemObject” in Excel 2011 for MAC? seem to indicate that using Scripting.FileSystemObject in Excel 2010 for the mac is not possible.

What other alternative is available so I can:

get a collection of all Excel files in a specific directory
iterate through each worksheet within each file and export it to a .csv file

Currently this is a six-step process for each file:

--how to create CSV files for all worksheets in a file:
1. open file
2. click "Developer"
3. click editor
4. click ThisWorkbook
5. copy in:
Sub save_all_csv()
    On Error Resume Next
    Dim ExcelFileName As String
    ExcelFileName = ThisWorkbook.Name
    For Each objWorksheet In ThisWorkbook.Worksheets
        Filename = "FILE-" & ExcelFileName & "-WORKSHEET-" & objWorksheet.Name & ".csv"
        objWorksheet.SaveAs Filename:="Macintosh HD:Users:edward:Documents:temporaryNoBackup:" & Filename, FileFormat:=xlCSV, CreateBackup:=False
    Next
    Application.DisplayAlerts = False
    Application.Quit
End Sub
6. click run (it closes by itself)

I'm looking for a way to automate this on the Mac, ideally, a (cron job?, service?) would open the excel file every 10 minutes, which would in turn look in a directory, convert all the other Excel files to .csv files, and then close by itself.

Without Scripting.FileSystemObject, how can I make this Excel-to-CSV conversion fully automatic on the Mac?

ErikE · Accepted Answer · 2018-05-10 20:51:01Z

2

The only way I can think of is using the "Dir" function. Since mac supports extra characters in their filenames, wildcards do not work with the "Dir" function. Here is a sample.

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

edited May 10, 2018 at 20:51

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

answered Jan 13, 2011 at 21:43

Fink

3,44622 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smpita · Accepted Answer · 2021-02-15 22:20:27Z

You can put the VBA in an add-in (.xlam file) that is attached to Excel itself, rather than the workbook. For your example code, the only modification would be to write against ActiveWorkbook instead of ThisWorkbook.

Sub save_all_csv()
    On Error Resume Next
    Dim ExcelFileName As String
    ExcelFileName = ActiveWorkbook.Name
    For Each objWorksheet In ActiveWorkbook.Worksheets
        Filename = "FILE-" & ExcelFileName & "-WORKSHEET-" & objWorksheet.Name & ".csv"
        objWorksheet.SaveAs Filename:="Macintosh HD:Users:edward:Documents:temporaryNoBackup:" & Filename, FileFormat:=xlCSV, CreateBackup:=False
    Next
    Application.DisplayAlerts = False
    Application.Quit
End Sub

You can also leverage auto_open() to automate binding a hotkey. Once that's done, you can just open a workbook, press a hotkey, and get your CSV files.

Public Sub auto_open()

'   Register hotkeys

'   See key codes here
'   https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-onkey-method-excel

'   ^ = CTRL
'   % = ALT
'   + = SHIFT

    Application.OnKey "^+e", "save_all_csv" ' Ctrl+Shift+E will call save_all_csv()

End Sub

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related