Scripting for Excel

Scripting Forums AppleScript | Mac OS X
#1

Hello all! Not sure if this is the correct forum to post this in, but I’m sure if it isn’t, one of the wonderful mods out there will get me in the right direction!

To make it short, I would like to create a script that will do 2 things-

  1. Delete column C
  2. Save the file as a UTF-16 txt file. not a copy, just save the file. I duplicate the files a save the originals unless something goes haywire.

I have sometime 80 files that I have to do this to, and it would be great to create a droplet that I can drop all the files into and have it take off and do its thing, or, if I need to run it as a script from within excel, I can do that as well.

I’m don’t really have much experience in Applescript, just enough to get something that looks like it should work, but doesn’t. Such as this little gem which is supposed to be a script that runs from the script menu in excel to just delete the C column.

tell application "Microsoft Excel"
	activate
	select column [C]
	delete EntireColumn
end tell

Any help would be greatly appreciated!

Matt

#2

Hi Matt. In applescript you normally tell something to do something. You tried to tell excel to do something but excel has workbooks, and each workbook contains sheets. So most of the time with excel you need to tell the sheet to do something. So try this…

tell application "Microsoft Excel"
	tell front workbook
		tell front sheet
			delete column 3
		end tell
	end tell
end tell

As far as saving the workbook, you can probably search the forums for clues about how to do that.

#3

That is good information. That got the delete working, so now I will do as you say, and search for the saving step. I appreciate the help in the little things, I always like to try it on my own first.

Thanks!

Matt

#4

Heres another deletion script. Note that the range is completely qualified and does not need to be inside a nested tell block.

tell application "Microsoft Excel"
	set myBook to get workbook "Workbook1.xls"
	set mySheet to get worksheet "Sheet1" of myBook
	set myRange to get range "C:C" of mySheet
	delete range myRange
end tell

This script will save the active workbook as a Mac style text file. I can’t find the file format for UTF-16

tell application "Microsoft Excel"
	activate
	try
		set uiFilePath to get save as filename file filter "TEXT" filter index 1
	on error
		--Cancel pressed
		return false
	end try
	try
		save workbook as active workbook filename uiFilePath file format text Mac file format
		return true
	on error
		return false
	end try
end tell