Excel Scripting - Cell Format Help Needed - (Content Format)

Scripting Forums AppleScript | Mac OS X
,
#1

I’m trying to change the content format of an Excel cell using Applescript. I need the Applescript to change all of column “A” of my Excel file to “Text” instead of “General” before I insert my text. Why? because my text (actually I’m inserting numbers) has leading zeros and the leading zero disappears when formated as “General” or “Number”.

I have tried to find this in the AS Guide for Excel with no luck.

Any help would be greatly appreciated,
CarbonQuark

#2

I’m still looking for how I did it, but I had a similar problem for a long time and never found the solution you’re looking for. I finally had to use a “trick” in Excel where if you start a string of numbers with the ’ (apostrophe) character it will format as a string and not a number and the apostrophe is invisible (displays in the formula bar but does not display in the cell or on printouts).

EDIT: Yeah just checked, my scripts that have had to import numeric strings (i.e. numbers treated as characters) have always found ways to prefix the apostrophe to the data before putting it into the cell, which forces Excel to format it as a string. This apparently is a feature of Excel, since it disregards the apostophe when displaying the field.

One other note…I seem to recall issues with PC-to-Mac use of Excel files. Vague recollections that the PC can force a numeric entry to “string” but the Mac can’t or somesuch, unless you use the apostrophe trick. So if you have to live in the PC world with these files, be sure to test thoroughly.

Sorry I couldn’t be more helpful, hope someone else comes back with a “real” solution that forces the issue.

#3

CalvinFold,

Thanks for the fast reply and information!

I found this in the AS guide for Excel. However, I can’t figure out the syntax.

Exerpt:
data type
Returns or sets the data type of the specified column in a list object. Read/write.
Can be one of the following:
no data type
whole number data
decimal data
list data
date data
time data
text data
currency data
calculated data
counter data

Thanks,
CarbonQuark

#4

Thanks again CalvinFold… I will use you solution until I find the correct syntax. - CQ

#5

I just discovered that I also need to format a column for “currency” instead of “general” using Applescript.

Any Ideas?

#6

Why won’t this work???


tell application "Microsoft Excel"
	set data type of column 1 of active sheet to currency data
end tell
#7

What about:

tell application "Microsoft Excel"
	activate
	Select Range "C1"
	set NumberFormat of Selection to "$#,##0.00"
end tell

(This admittedly is from Excel:Mac v.X)

I tried your script, the error said it didn’t recognize “active sheet.”

#8

Ressurrecting

On Excel 2008 on Mountain Lion, tested, works


tell application "Microsoft Excel"
	tell sheet 1
		tell column 1
			set number format to "@" --this works to set plain text format with leading zeros
		end tell
	end tell
end tell