Format Excel Sheet

Homer712 · March 2, 2025, 3:54pm

I’ve got an Excel spreadsheet that has a large number of rows, 477 to be exact. What I’m trying to do via an AppleScript, is copy the first 30 or so rows (could be adjusted up or down depending on which Mac system I’m on), and paste them into a new column, and do this repeatedly until all 477 rows have been copied into new columns.

I’ve done a good amount of searching, both here and in general on the web, but apparently, no one has ever asked for this particular configuration.

Excel 2019, Version 16.78.3
Sonoma 14.7.4

Mockman · March 2, 2025, 8:32pm

Can you clarify this a bit? A ‘row’ extends for the length of the spreadsheet. What do you mean by putting rows into a new column? A row is already in every column. Do you mean taking a table that is about 480x1 and making it 30x16?

FWIW, I just answered a similar question last night that uses the cut range command.

Homer712 · March 2, 2025, 8:47pm

Mockman, what I am looking to do is this. Copy A1 thru A10 and B1 thru B10, and then paste them into C1 thru C10 and D1 thru D10. See Screenshot.

With that basic script, I should be able to modify it to really do what I need, which is a series of copy/paste commands that will end up making columns that are viewable on the screen, rather than having to scroll thru a row that’s 477 long.

Hope this makes sense. And once I have the basic copy/paste down, I can also work on the “cut” command.

Mockman · March 3, 2025, 5:34am

Try this:

It does the following:

counts the rows and cycles through them 30 at a time
uses intersect to specify each bunch of 2x30
gets the destination cell for each cycle
uses cut range to move each bunch to its destination cell

tell application "Microsoft Excel"
	
	set cr to current region of cell "A1"
	set ar to areas of cr
	
	set raw to rows of (item 1 of ar)
	set lar to length of raw -- how many total records
	set bunchCount to 30 -- how many records at a time
	set rowBunches to (lar / bunchCount)
	
	set initialCol to "A:B"
	repeat with xx from 1 to (rowBunches - 1)
		set mound to bunchCount * (xx)
		
		set block30 to intersect range1 range initialCol range2 range ((mound + 1) & ":" & mound + bunchCount as text)
		
		-- tracking
		log "xx: " & xx
		log "bunchCount: " & bunchCount
		log "mound: " & mound
		
		-- cell to dump block30
		set dumpCell to get offset range "A1" column offset (xx * 2)
		-- cut block30 and dump in dumpCell
		cut range block30 destination of cut dumpCell
		
	end repeat
	
end tell

I set a variable for the 30 rows but not for the 2 columns. That can be modified as needed.

NB Updated to fix variable name as noted below

Homer712 · March 3, 2025, 12:22pm

Mockman, I get an error about “bunches” not being defined.

Mockman · March 3, 2025, 12:24pm

Oops… I changed the variable names as they didn’t make much sense. ‘bunches’ was changed to ‘rowBunches’ but I guess I missed one. Make that change and give it another try.

Homer712 · March 3, 2025, 12:57pm

That now works perfectly (and fast), thank you.

Question, is it possible to format column widths via an AppleScript. If you could just give me a few of the basic commands, I can play around with it over morning coffee. I’m thinking I’d like column A to be 30 wide, column B 20 wide, and just repeat that through the entire spreadsheet or at least through to AE and AF.

Mockman · March 3, 2025, 1:21pm

Here are a couple of ways to set column width.

Note that you don’t necessarily have to figure out the workbook or sheet as it will assume the front workbook and the active sheet.

tell application "Microsoft Excel"

	set column width of range "C3" to 30
	
	set column width of range "E:G" to 20
	
	set column width of range "A:A,C:C,E:E" to 22
	
	set column width of column 2 to 30

end tell

You could probably do something like in the script above, where you would do them in bunches in a loop, or even just one column at a time.

And of course, if you plan on using such a format in multiple workbooks over time, make a template.

Homer712 · March 3, 2025, 3:04pm

Mockman, with your help I was able to combine everything into one script that works perfectly, thank you!

tell application "Microsoft Excel"
	
	set cr to current region of cell "A1"
	set ar to areas of cr
	
	set raw to rows of (item 1 of ar)
	set lar to length of raw -- how many total records
	set bunchCount to 30 -- how many records at a time
	set rowBunches to (lar / bunchCount)
	
	set initialCol to "A:B"
	repeat with xx from 1 to (rowBunches - 1)
		set mound to bunchCount * (xx)
		
		set block30 to intersect range1 range initialCol range2 range ((mound + 1) & ":" & mound + bunchCount as text)
		
		-- tracking
		log "xx: " & xx
		log "bunchCount: " & bunchCount
		log "mound: " & mound
		
		-- cell to dump block30
		set dumpCell to get offset range "A1" column offset (xx * 2)
		-- cut block30 and dump in dumpCell
		cut range block30 destination of cut dumpCell
		
	end repeat
	
	tell application "Microsoft Excel"
		
		set column width of range "A:A,C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE" to 30
		set column width of range "B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF" to 20
		
	end tell
	
end tell

Homer712 · March 3, 2025, 5:34pm

Mockman, I thought I could do this last little bit on my own, but after a lot of searching, I’ve come up empty. So, what is the proper command to “center align” the text of a group of cells. I could add that right after the column width commands and have the spreadsheet be completely formatted.

Mockman · March 3, 2025, 6:28pm

By the way, you can remove the tell/end tell statements around the set column width… commands as they are already inside an excel tell block.

As for text alignment, horizontal alignment is a property of ‘range’ and of ‘cell’.

The command would like like this:

	set horizontal alignment of range "C4:D4" to horizontal align center
	--> horizontal align center

If you get the properties of a range, you can see that among them is the horizontal alignment.

As an aside, if you just run this command and look at the result in Script Editor, you will see that some of the property values are purple and some are blue. The purple values also possess their own properties, although not every one of them does. For example, horizontal align center has itself as a property.

But for many of them, especially those with ‘object’ in their name, you can dig up a lot of additional properties.

	properties of range "C4:D4"
	{class:range, add indent:false, ¬
	areas:{range "[Workbook1]Sheet1!$C$4:$D$4" of application "Microsoft Excel"}, first column index:3, ¬
	column width:10.0, current array:missing value, ¬
	current region:current region of range "C4:D4" of application "Microsoft Excel", ¬
	dependents:missing value, direct dependents:missing value, direct precedents:missing value, ¬
	entire column:entire column of range "C4:D4" of application "Microsoft Excel", ¬
	entire row:entire row of range "C4:D4" of application "Microsoft Excel", ¬
	font object:font object of range "C4:D4" of application "Microsoft Excel", formula:{{"3", "4"}}, ¬
	formula array:missing value, formula label:missing value, formula hidden:false, ¬
	formula local:{{"3", "4"}}, formula r1c1:{{"3", "4"}}, formula r1c1 local:{{"3", "4"}}, ¬
	has array:false, has formula:false, height:15.0, hidden:missing value, ¬
	horizontal alignment:horizontal align general, ¬
	indent level:0, interior object:interior object of range "C4:D4" of application "Microsoft Excel", ¬
	left position:130.0, list header rows:0, location in table:missing value, locked:true, ¬
	merge area:missing value, merge cells:false, name:missing value, named item:missing value, ¬
	number format:"General", number format local:"General", text orientation:orientation horizontal, ¬
	outline level:missing value, page break:missing value, pivot field:missing value, pivot item:missing value, pivot table:missing value, precedents:missing value, ¬
	prefix character:"", query table:missing value, first row index:4, row height:15.0, ¬
	show detail:missing value, shrink to fit:false, style object:style object of range "C4:D4" of application "Microsoft Excel", ¬
	summary:missing value, string value:{{"3", "4"}}, top:45.0, use standard height:true, ¬
	use standard width:true, validation:validation of range "C4:D4" of application "Microsoft Excel", ¬
	value:{{3.0, 4.0}}, value2:{{3.0, 4.0}}, vertical alignment:vertical alignment bottom, ¬
	width:130.0, worksheet object:worksheet object of range "C4:D4" of application "Microsoft Excel", ¬
	wrap text:false, Excel comment:missing value, phonetic object:phonetic object of range "C4:D4" of application "Microsoft Excel", ¬
	reading order:left to right, list object:missing value, count large:2, ¬
	display format:display format of range "C4:D4" of application "Microsoft Excel"} ¬

Homer712 · March 3, 2025, 7:36pm

Thank you again, the “align center” script works perfectly.

Edit: Although the script seemed to work at first, over a few tries, it no longer functioned reliably. Here’s what I ended up with.

tell application "Microsoft Excel"
	
	set myRangeCells to range ("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF")
	set horizontal alignment of myRangeCells to horizontal align center
	
end tell

Added that to the main script (right after the column width sections, and I have a fully functioning script. Again, thanks for all your assistance.