Macro crashes in Excel for Mac

I am thrilled at the prospect of creating a key for bee genera in this software. Actually, at re-creating the key: my first effort is here: http://xper3.com/xper3GeneratedFiles/publish/identification/-284130055051328616/mkey.html

This is a nice design, but has a seemingly fatal flaw: technical glitches for which there is zero support. I’ve emailed the www site many times with no response.

But my Identikit effort is stalled. I have installed the software and worked up to the point where I run the “Save worksheets as CSV” macro. My excel freezes and I have to force quit.

I have the latest Office 365 excel. I have an M1 mac mini with Monterey OS. I have re-started, re-installed, to no effect.

SHOULD this work? Is it a mac, Monterey, or M1 compatibility problem?

Thanks for any clues, David

Hi David. I’ve no experience of Mac but I do remember Mac users having problems with the macros in the spreadsheet in the past, probably because of security settings. In several cases they had to resort to working directly in the five CSV files instead - skipping the Excel spreadsheet. As you know, when you download the software there is an example KB - biscuits.xlsm. The ‘save worksheets as CSV’ button simply saves the five worksheets in this excel file as separate CSVs:

  • taxa.csv
  • characters.csv
  • values.csv
  • media.csv
  • config.csv

CSVs corresponding to the state of the supplied buiscuits.xlsm are already supplied. So you you can use copies of those CSVs as templates for your own knowledge base and just work directly with them. Bin the XLSM file, because if the macro ever did start working and you used it, it would overwrite any work you do directly in the CSVs.

Thanks a million. I allowed myself to be derailed at the installation phase. If I had read on in the documentation, I would have figured this out.

I will note that in the meantime I made an attempt to install in Windows 11, and ran into “couldn’t create directory” and other obstacles. Possibly related to Windows running via Parallels on the mac. But with your solution, I will happily abandon the Windows route.

–David

The module below solves the problem of the macro crashing the excel file on mac, to use in place of the macro built in to the identikit excel. I also created some automator actions in mac OS, so my steps are:

Run action that copies biscuit excel to a folder.
Open that excel and run the macro
Run action that copies the resulting csv’s to the kb folder.

This is clunky, and I am sure the extra steps could be addressed with edits to the module. But still faster than manually copying each worksheet to csv.

BTW, the original excel may fail because it tries to save csv’s in the kb folder. Macs are apparently fussy about where you can write new files from excel.

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

SaveToDirectory = “./”

For Each WS In ThisWorkbook.Worksheets
WS.SaveAs SaveToDirectory & WS.Name & “.csv”, xlCSV
Next

End Sub

1 Like