As I said in Automating Microsoft Excel, I thought I might write some more about automating Excel.
Recall I wrote about it because finding snippets of code to do what you want is difficult. So if I can add to that meagre stockpile on the web, I’m going to.
That other post was about automating graph manipulation. This post is about another aspect of automating Excel.
The Problem I Wanted To Solve
Recently I’ve had several instances where I’ve created a CSV (Comma-Separated Value) file I wanted to import into Excel. That bit’s easy. What made these instances different (and harder) was that I wanted to import them into a bunch of sheets. Think “15 sheets”.
This is a difficult problem because you have to:
- Figure out where the break points are. I’m thinking a row with only a single cell as a good start. (I can make my CSV file look like that.)
- Load each chunk into a separate new sheet.
- Name that sheet according the the value in that single cell.
- (Probably) delete any blank rows, or any that are just a cell with (underlining) “=” or “-” values.
I haven’t solved that problem. When I do I’ll be really happy. I expect to in 2021.
The Problem I Actually Solved
Suppose you have 15 sheets. There are two things I want to do, given that:
- Rapidly move to the first or last sheet.
- Move the current sheet left or right or to the start or end.
The first is about navigation when the the sheets are in good shape. The second is about getting them that way. (When I manually split a large CSV file the resulting sheets tend not to be in the sequence I want them in.)
As noted in the previous post I’m using the Metagrid app on a USB-attached iPad. Here is what my Metagrid page for Excel currently looks like:
In the blue box are the buttons that kick off the AppleScript scripts in this post. As an aside, note how much space there is around the buttons. One thing I like about Metagrid is you can spread out and not cram everything into a small number of spots.
I’m not going to claim my AppleScript is necessarily the best in the world – but it gets the job done. Unfortunately that’s what AppleScript is like – but if you are able to improve on these I’m all
Move To First Sheet
tell application "Microsoft Excel" select worksheet 1 of active workbook end tell
Move To Last Sheet
tell application "Microsoft Excel" select worksheet (entry index of last sheet) of active workbook end tell
Move Sheet To Start
tell application "Microsoft Excel" set mySheet to active sheet move mySheet to before sheet 1 end tell
Move Sheet To End
tell application "Microsoft Excel" set mySheet to active sheet set lastSheet to (entry index of last sheet) move mySheet to after sheet lastSheet end tell
Move Sheet Left
tell application "Microsoft Excel" set mySheet to active sheet set previousSheet to (entry index of active sheet) - 1 move mySheet to before sheet previousSheet end tell
Move Sheet Right
tell application "Microsoft Excel" set mySheet to active sheet set nextSheet to (entry index of active sheet) + 1 move mySheet to after sheet nextSheet end tell
Those snippets of AppleScript look pretty simple. However, each took quite a while to get right. But now they save me time on a frequent basis. And they might save you time.
They are all Mac-based but the model is similar to that in VBA. If you’re a Windows person you can probably replicate them quite readily with VBA.
And perhaps I will get that “all singing, all dancing” Import-A-CSV-Into-Multiple-Sheets automation working. If I do you’ll
hear read about it here.