Re: Excel format cels as number with Applescript
Re: Excel format cels as number with Applescript
- Subject: Re: Excel format cels as number with Applescript
- From: Paul Berkowitz <email@hidden>
- Date: Sun, 28 Jun 2009 20:31:23 -0700
- Thread-topic: Excel format cels as number with Applescript
Title: Re: Excel format cels as number with Applescript
This is tested in Excel 2008, but it should work for 2004 as well. There were some bugs with rows and columns in 2004, but I don't think they affected what's going on in this script:
tell application "Microsoft Excel"
set hc to find (row 1 of used range of active sheet) what "Article number"
set n to first column index of hc
set f to first column index of (column 1 of used range of active sheet)
set col to column (n - f + 1) of used range of active sheet
set number format of col to "0"
end tell
The funny stuff with (n - f + 1) is just in case you should happen to have empty columns at the left of your sheet (column A). The first column index is an "absolute" number on the sheet, so if the used range does not start in column 1 you need to get it and subtract the number of empty columns to the left. That's extremely unlikely, surely. If you have normal worksheets where every column starting at the leftmost has something in it, then this simpler script will work fine instead:
tell application "Microsoft Excel"
set hc to find (row 1 of used range of active sheet) what "Article number"
set n to first column index of hc
set col to column n of used range of active sheet
set number format of col to "0"
end tell
You can't use the 'entire column' property, which would be even simpler than getting the column index - it doesn't seem to resolve to an actual column or range. And you shouldn't format the whole column of the sheet (at least not in Excel 2008, which has 65,000+ rows - it takes forever and everything is paralyzed while it works away at it.) The scripts act only on the used range of the sheet, which is immediate. So if you add more rows you'd have to run the script again - best wait until all the rows you need are added.
You can check how the number format works in AppleScript. For example, the one you started out with was "0,00000E+13". "0" is for regular integers without decimal places, what you're looking for.
--
Paul Berkowitz
> From: Bert Groeneveld <email@hidden>
> Date: Sun, 28 Jun 2009 22:50:35 +0200
> To: AppleScript-Users <email@hidden>
> Subject: Excel format cels as number with Applescript
>
> Hello, can anyone poste Applescript example code for the following:
> While talking to the active document of Microsoft Excel 2004:
> I want to refer to the column whose first cel contains exactly the
> text "Article number"
> I want to format all the cels of that single column as Number with
> zero decimal places.
> (cel 2 of that column for example reads "8,08191E+13" and I want it to
> be in the normal number format so that it reads "80819094600000")
> That's all I want. Can anyone help please?
> Kind regards, Bertus.
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> AppleScript-Users mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
> Archives: http://lists.apple.com/archives/applescript-users
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden