Excel Formating

[dishusa]

Cannot find an excel’s dictionary any terms that support formating cells. I am trying to get date fromats that are imported in a colum as “12-Jan-2003” to show as “01/12/2003”. I know an excel macro can do this, but I would like to applescript it as part of a much larger script project.

any suggestions?

[jonn8]

Excel (like Word) is recordable so most Excel questions can be answered by opening a new document in Script Editor, clicking the “Record” button and then do what ever you want to do in Excel. The AppleScript commands for your actions will be added to your new document. Just click “Stop” in SE to stop the recording. Anyway, here’s the code you need. You’ll need to adapt it, of course, to your target cells.

tell application “Microsoft Excel”
set NumberFormat of “R1C1” to “mm/dd/yy” – R = Row; C = Column so R1C1 = Cell A1
end tell

–this script was automatically tagged for
–color coded syntax by Script to Markup Code

Jon

[dishusa]

thanks again.

C

[dishusa]

Ok, this works fine until the save command. A dialogue box asks to replace an existing file with the same name.

tell application “Microsoft Excel”
Activate
Select Range “C2”
set NumberFormat of Selection to “mm/dd/yyyy”
Save ActiveWorkbook In “Macintosh HD:Users:dishusa:Desktop:commisions.csv” As xlCSV without CreateBackup
end tell

I did not see a property like “with replacing” in the excel script dictionary. Is this possibel?

[jonn8]

This is common to most apps:

tell application “Microsoft Excel”
Close document 1 saving Yes
end tell

Jon

[Ray_Barber]

This is common to most apps:

tell application “Microsoft Excel”
Close document 1 saving Yes
end tell

Jon

I am struggling on a related problem. When copying more than 100 cells and closing a workbook, Excel warns:

There is a large amount of information on the Clipboard.

Do you want to be able to paste this information into another program later?

• To save it on the Clipboard…

For VBA, Microsoft recommends for avoiding this dialog box to either:

1. Copy a Single Cell (does not help, I do want to paste)
2. Exit from CutCopyMode (not sure how this works in AppleScript)
3. Save the Workbook - before closing it

I guess number 3 would work, but when I save, I get the same “replace an existing file” dialog. Does anybody know how to avoid (or answer) both “clipboard save” and “replace file” dialog?