Automating Microsoft Excel Some More

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:

1. 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.)
2. Load each chunk into a separate new sheet.
3. Name that sheet according the the value in that single cell.
4. (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.

The Scripts

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 ears eyes.

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

Conclusion

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.