When I download any report from NetSuite, my Mac interprets it as a ".xml" - see screenshot.
When I go to save it, you can see in the "File Format" pop-up menu, the file type is (.xml). Is there a way to get KM to consistently select "Excel Workbook (.xlsx)"? The location of the save-as dialog box could be anywhere on my laptop screen or on an additional screen. I can't be the only person with this problem.
Thanks,
Patrick
Hey Patrick,
Try this first:
Report back if that doesn't work.
-Chris
Thanks.
That setting was already set to .xlsx but since Excel thought it was a .xml file when it opened to file, that's what is in the File Format pop-up. (The pain in the neck part is when I rename the file and put it in the right folder and then hit save, I get a dialog that asks if I want to keep .xml. When I click "No", I have to start the process all over.)
Thanks,
Patrick
What happens if you change the file extension to .xlsx before trying to open it in Excel?
Hey Patrick,
Yes, this is a very annoying process to have to go through – but then again – you have Keyboard Maestro...
Make sure to read the macro notes.
The macro is designed to run on a single selected file in the Finder.
Keep in mind that the behavior is very flexible, but this requires sufficiently detailed specifications.
-Chris
Save the Front Workbook as a XLSX File v1.00.kmmacros (9.2 KB)
This didn't work for me (yet). I'm going to look at it and see if it's something on my end. I get an error with - ERROR = too many or too few dialogs.
The typical workflow for me is to click a button in NetSuite to download the report that I'm viewing in Excel format. Then I open it up in Excel directly from Safari or Chrome. If I open the file in BBEdit, I can see that the first line has the type as ".xml". Not sure why NetSuite does this, Very annoying.
NetSuite is creating the report in Excel's previous document format, which was an XML file. So everything's working exactly as intended -- back in 2004 
(Just to confuse things further, the current format is also XML, except buried in a zip file...)
If you can post your macOS and Excel versions I'll see if I can test @ccstone's script against what you're using in case the usual MS "improvements" are getting in your way.
My macOS is Ventura, Version 13.1.
My Excel version is a subscription version, 16.67.
Thanks for the help.
Patrick
If you already have the .xml file open in Excel as the frontmost workbook, converting it is as simple as
tell application "Microsoft Excel"
tell workbook 1
set newName to ((characters 1 thru -4 of (get name)) as text) & "xlsx"
save workbook as filename newName file format Excel XML file format
end tell
end tell
...which will save it as an xlsx file in the original's directory, with the same name but changed extension.
You could put that in a KM macro as a single AppleScript action, run by a hotkey combo.
If you want to build that into a larger workflow -- for example, I think your first screenshot is an Excel warning dialog because the extension doesn't match the file type, and you could avoid that by changing it to .xml after download but before opening -- then describe the steps and I'm sure people will chip in.
Hey Nige,
Ha! It's been too long since I scripted Excel regularly – I forgot you could easily save to a specific location without using the UI.
I must have had driving the UI on the brain...
On macOS 10.14.6 Mojave using Excel 16.16.27 your script fails to save the new workbook in the same container folder as the old one – instead it gets saved to Excel's default location:
In order to save next to the original I have to give the save command a full path.
Furthermore:
I've rewritten my script to not require a Finder selection. Instead it collects any .xlx files in the ~/Downloads folder and processes them.
If processing is successful the original .xls files are moved to the trash.
I'll post that after a while – probably in the morning, but we'll see...
-Chris
Above was done on macOS 13.1 and Excel 16.67, to match with OP's setup, but is also true for macOS 10.14.6 and Excel 16.35 -- I'd guess things changed in either Excel 16.17 (Office 2019).
IMO the "old way" was always wrong -- saving a new file should default to the default document location, saving an already-saved saved file should default to the file's location (even a "Save As..."). And that should be replicated in the app's AS support.)
It should do that on any system. Complete guess without knowing the software, but I think the problem is server-side -- NetSuite is creating Excel 2004 files (single XML) but giving them the xls name extension instead of the correct xml. OP downloads the file, tries to open it, and Excel (quite properly) is warning the user of the name/format mismatch.
IMO both that and the potential "where is it saved?" problem will be avoided by a complete workflow -- either a manual trigger on selected file or "watched folder" monitoring of Downloads for an arriving .xls, rename to .xml, open in Excel and "Save As" to an explicit location as .xlsx, delete original.
You could even build in additional checks, eg "chosen file has .xls extension but first line starts <?xml", or maybe "came from specific server", to make sure you don't process the wrong files.
This might be a crazy question but is there a way to edit the file and remove the <?xml tag and replace it with either xls or xlsx? The file name has a ".xls" extension already and that would solve all the problems.
BTW, I love the idea of a watched folder where this all happens prior to opening the file the first time in Excel and therefore avoiding the first dialog box.
Thanks for the input
Doesn't work like that. It is an XML file (that tag just states the version of XML being used) and changing it will just mean it's a broken XML file. The incorrect extension is a different problem and should be sorted out in the server software -- but, given that it hasn't been fixed in 10+ years, let's not rely on that happening...
Again, if you describe your workflow -- "I click a button in the web app, the file is downloaded to my Downloads folder, I want it automatically saved as a 'proper' Excel file in the "Reports/YYYY-MM" (using the current year and month) folder with the same name as the downloaded file but with my initials prepended" -- someone will come up with some ideas to help.
Thanks for the education.
Typical workflow is to be using NetSuite while in Chrome. Run a report and hit the Export as Excel button in NetSuite. The report imediatley downloads into the Downloads folder and is visible in the Chrome downloads bar at the bottom of the window. I would typically click on the file in Chrome and the downloaded report opens in Excel with the warning dialog box. I tap the spacebar to say Yes in the dialog box and use the file.
I just created this brute force Macro using the Tab key to get around the Save as... dialog box instead of trying to find the pop-up with the mouse.
Not elegant but it seems to work. I'll make this the default behavior for Cmd-Shift-S in Excel only.
Hey Guys,
Okay, here's a revised AppleScript that does all the work.
~/Downloads folder.-Chris
Save the Front Workbook as a XLSX File v2.00.kmmacros (11 KB)
