clearing formats in excel / setting cell format to general

Scripting Forums AppleScript | Mac OS X
,
#1

Hi Folks,

I need to clear the format of all cells in a given worksheet as part of an applescript.

This step is needed so that when I copy a range from one sheet and concatenate it with other data the format all play nicely together

If i were to do this manually I would select all in the active sheet and the go to format cells and choose the number format general.

does any one know how to do this using applescript?

thanks in advance

nic

#2 
tell application "Microsoft Excel"
	
	set number format of (cells of active sheet) to "General"
	
end tell

Excel’s AppleScript Reference guide can be downloaded from
http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7

#3

Hi Mike

thanks for your response - but it’s not working. I keep getting an error message that the object I am trying to access doesn’t exist.

I modified it a little to read:



tell application "Microsoft Excel"
	
	set activesheet to worksheet "All"
	
	
	set number format of (cells of activesheet) to "General"
	
end tell

any suggestions? (greatly appreciated)

i figured out another way of doing this that works, but it is fairly rudimentary (and chunky)



set downarrow to ASCII character 31 -- Down arrow
tell application "Microsoft Excel"
			activate
			activate object sheet "All"
			
			tell application "System Events"
				
				keystroke "a" using {command down}
				keystroke "1" using {command down}
				keystroke tab
				keystroke tab
				keystroke downarrow
				keystroke "g"
				keystroke return
			end tell
end tell

I have had a look at the MS-Excel manual. Sometimes it has been helpful, in this case, not so.

#4

A more generalized syntax would be

tell application "Microsoft Excel"
	set number format of (cells of (sheet "Sheet1" of workbook "Workbook1.xls")) to "general"
end tell

or

tell application "Microsoft Excel"
	set number format of (cells of (sheet "Sheet1" of active workbook)) to "general"
end tell
#5

thanks Mike,

this works :slight_smile:

I also had something else recommended to me which (seems to) work nicely


		tell active sheet
				clear range formats used range
			end tell -- clears formatting of active sheet. reduces formatting issues when concatenating further in the script.
#6

See the answer HERE