Getting first empty "A" Cell from an Excel file......

I need a script that will open an excel file and tell me what the next cell number (an empty cell) in column A will be.

thanks,

eekabob

Something like this may work for you. It doesn’t seem to like a completely blank worksheet, however:

tell application "Microsoft Excel"
	Open "Path:To:File"
	get Row of (Find Column 1 What "" after Cell "r1c1" LookIn xlValues LookAt xlWhole direction xlNext)
end tell

I open the script in editor and when I try to compile it it’s telling me "Syntax Error, Expected “,” but found identifier. Forgive me, I kind of new to applescript…

What version of Excel are you using? (And I guess while we’re at it, which Mac OS and version of the script editor?)

Also, when you dismiss the error, what part of the script is highlighted?

Using OSX 10.3.9 and Script editor 1.9.3…the highlighted part is “LookIn”

The dictionary for Excel change significantly from Office X to Office 2004. It looks like you are trying to use the old syntax with Excel 2004 and that will not work. The following script works for mw eith Excel 2004


set DocPath to "Perrin:Test.xls"

tell application "Microsoft Excel"
	activate
	open DocPath
	set TargetRange to column 1 of the active sheet
	set AfterCell to cell "A1" in the active sheet
	set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction search next
	set the formula of FoundRange to "I found a blank entry!"
end tell

Hoe this helps

Jim “Murf” Murphy

that worked great…how can I have applescript tell me what cell number it is? example “A122”

You add the get address call to the foundRange


set DocPath to "Samwise:Test.xls"

tell application "Microsoft Excel"
	activate
	open DocPath
	set TargetRange to column 1 of the active sheet
	set AfterCell to cell "A1" in the active sheet
	set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction search next
	set the formula of FoundRange to "I found a blank entry!"
	set foo to get address FoundRange
	return foo
end tell


Enjoy!

Jim “Murf” Murphy

Thanks Jim, your script worked great!!!

Eekabob

Hey guys!
I’m pretty new here, I just registered, I got into scripting like a month ago, and here is my problem, I want the script to return the row number of a row I’m in I find the last value, and I need the row number of that, I can’t do anything with the whole address, I need it for a cycle… how can I extract it out of the foo variable, or at all?
thanks, I’m on panther btw…

I have modified my original example script and this should show how to get all of the information you wante from it

Enjoy!


tell application "Microsoft Excel"
	activate
	open DocPath
	set TargetRange to column 1 of the active sheet
	set AfterCell to cell "A1" in the active sheet
	set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction search next
	
	set rowIdx to first row index of FoundRange
	set colIdx to first column index of FoundRange
	set foo to get address FoundRange
	
	set the formula of FoundRange to "The row index I found is " & rowIdx & " and the column index is " & colIdx & " and the address is " & foo
	
	
end tell

Jim “Murf” Murphy

Thanks Dude!
You’ve been a greate help… you know what else I could use…
When excel prompts you for something how do you click one of the buttons it allows you to, I use the set display alerts to false command to get default answers
but when I need a pivot table report, I need to mechanically click finish, because it doesn’t allow it to go through…
lemme show you what I’m talking about:
if I give an order

show (get dialog dialog pivot table wizard)

it’ll show the wizzard but disregarding the fact that I have

set display alerts to false
in the begining… what am I to do?
Also how can I get the current date into a variable in a format like 050720 or 2005 07 20 or whatever form that always contains the current date, but not the hour and second etc…

BTW: thanks for the help, it’s helped me alot, you are too kind!

You got a couple of quests in that last post :smiley:

Applescript in general does not script user interfaces but rather the underlying model of the applicaitons. This is certianly true of Excel. To script UI you need to use system events. I personally have not done much UI scripting so I suggest the article in the June 2005 MacTech magazine. You can order the magazine at http://www.mactech.com. I find this magazine quite useful.

I’m going to assume that you are asking about using UI scripting for pivot tables because you do not know how to script them. Here is a script that shows some of the things that you can do to script pivot tables.


(* 
	Pivot Table Example Script
	
	Please note that this code is just an example
	if it does something you find useful that would
	be an fortunate accident :-)
	
*)

tell application "Microsoft Excel"
	activate
	
	-- Start the Example fresh by making a new workbook
	set CWB to make new workbook
	
	-- Name the active sheet so we can get back to it easier
	set the name of the active sheet to "PivotTable"
	set PTSheet to the sheet named "PivotTable" of CWB
	
	-- Make a new sheet to hold our data
	set newsheet to make new worksheet at the end of CWB
	
	-- Name the sheet so we can get back to it easier
	set the name of the active sheet to "Database"
	set DBSheet to the sheet named "Database" of CWB
	
	-- Set the view of the active window to 'normal" easier to see
	set the view of the active window to normal view
	
	-- Fill in data into our data sheet.  for a "real" script
	-- This would be where you get and put your data
	tell DBSheet
		
		set the formula of range "A1" to "Bug #"
		set the formula of range "B1" to "Status"
		set the formula of range "C1" to "ENVIRON"
		set the formula of range "D1" to "Client"
		
		set the value of range "A2" to 1
		set the value of range "A3" to 2
		set the value of range "A4" to 3
		--select range "A2:A4"
		
		autofill range "A2:A4" destination range "A2:A150"
		
		set the formula of range "B2" to "CLOSED"
		autofill range "B2" destination range "B2:B150"
		set the formula of range "B37" to "ACTIVE"
		set the formula of range "B56" to "ACTIVE"
		set the formula of range "B135" to "ACTIVE"
		
		set the formula of range "C2:C150" to ¬
			{{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC/WIN"}, ¬
				{"MAC"}, ¬
				{"MAC"}, ¬
				{"MAC/WIN"}}
		
		set the formula of range "D2:D150" to ¬
			{{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#NULL!"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"WIN"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#N/A"}, ¬
				{"#DIV/0!"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#VALUE!"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#REF!"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#NAME?"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"#NUM!"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{""}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"UNK"}, ¬
				{""}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"WIN"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"WIN"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{""}, ¬
				{""}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}, ¬
				{"ALL"}}
	end tell
	
	-- Now that we have some data switch over to the 
	-- Pivot Table sheet and make a new pivot table
	select PTSheet
	set PTable to make new pivot table at PTSheet with properties ¬
		{source data:range "Database!A1:D150", table range1:range "PivotTable!A1:B2", name:"Table1"}
	
	-- Now that we have a pivot table let's add some fields	
	add fields to pivot table PTable row fields {"Client"} column fields {"Environ"} page fields {"Status"} with add to table
	add fields to pivot table PTable page fields {"Client", "Environ"} without add to table
	add fields to pivot table PTable column fields {"Status"} with add to table
	
	-- Let's add a computed value
	set the pivot field orientation of the pivot field named "Bug #" of PTable to orient as data field
	set the function of data field 1 of PTable to do count numbers
	set the name of data field 1 of PTable to "Count of Bug #"
	
	-- Let's rename the ENVIRON field to "Environ new"
	set the name of the pivot field named "ENVIRON" of PTable to "Environ new"
	
	-- make the blank items of client invisible
	set pivItemName to "(blank)"
	set visible of the pivot item named pivItemName of pivot field "Client" of PTable to false
	
	-- change the name of the pivot item named Win to Windows
	set the name of the pivot item named "Win" of the pivot field named "Environ new" of PTable to "Windows"
	
	-- Change the position of the pivot item named "Windows" to the top
	set position of the pivot item named "Windows" of the pivot field named "Environ new" of PTable to 1
	
	-- Nest Client underneath Environ in Page Dimension.
	set the position of the pivot field named "Client" of PTable to 2
	
	--Pivot Client to column field
	set the pivot field orientation of the pivot field named "Client" of PTable to orient as column field
	
	-- Set the position of the two column fields
	set the position of the pivot field named "Client" of PTable to 1
	set the position of the pivot field named "Status" of PTable to 1
	
	-- Make Eviron a row field
	set the pivot field orientation of the pivot field named "Environ new" of PTable to orient as row field
	
	-- set some of the subtotals for the "Environ new" field
	tell pivot field named "Environ new" of PTable
		set subtotals subtotal index sum subtotal with value
		set subtotals subtotal index count subtotal with value
		set subtotals subtotal index average subtotal with value
	end tell
	
	-- make Client a row field
	set the pivot field orientation of the pivot field named "Client" of PTable to orient as row field
	
	-- Hide the status field
	set the pivot field orientation of the pivot field named "Status" of PTable to orient as hidden
	
	-- add a second data field
	set the pivot field orientation of the pivot field named "Bug #" of PTable to orient as data field
	set the function of data field 1 of PTable to do sum
	set the function of data field 2 of PTable to do count numbers
	set nameOfDataField1 to the name of data field 1 of PTable
	
	set DataPF to the pivot field named "Data" of PTable
	tell DataPF
		set the position to 1
		set the position to 2
		set the pivot field orientation to orient as column field
	end tell
	
	set the pivot field orientation of the pivot field named nameOfDataField1 of PTable to orient as hidden
	
	-- Show details for the all sub key
	set the inner detail of PTable to "Status"
	set show detail of range "B6" of the active sheet to true
	
	-- hide then show details
	set show detail of range "B6" of the active sheet to false
	set show detail of range "B6" of the active sheet to true
	
end tell

You other question was about date formats. Here is a scirpt that shows you how to set the format to whatever you want.


tell application "Microsoft Excel"
	
	set the formula of cell "A1" of the active sheet to "=NOW()"
	
	-- set the number format to the way you would like to see the date
	
	-- to get the date to look like 050720 do the following
	set the number format of cell "A1" of the active sheet to "yymmdd"
	
	-- to get the date to look like 2005 07 02 do the following
	set the number format of cell "A1" of the active sheet to "yyyy mm dd"
end tell

Hope this helps!

Jim “Murf” Murphy

Hey there thanks again!
This was what I would’ve needed which wouldn’t work for me before for some reason…

set PTSheet to the sheet named “Sheet1” of active workbook
set PTable to make new pivot table at PTSheet with properties ¬
{source data:range “Sheet2!A:A”, table range1:range “PivotTable!A1:B2”, name:“Table1”}

the rest I already had down, so thanks…
about the date format, I already know how to set it (btw do you have IM or MSN or something so maybe we could chat once, I’d like to show you some stuff I’ve done with applescript I think it’s pretty cool?) my question is do I know how to set a variable automatically like say for today 072405
but when I run the script tomorrow it’d make it 072505 the day after 072605 you know what I mean… because when I tried to set something to get date it would always put the time in it and blah blah blah… I need it to add to file names and directories…
Also if you have more free time on your hands in your script which you showed me, once you’ve scrolled down to the very end, and found the last cell in a column A, how can I set a range to all the rows of every cell bigger than the last one…
Thanks again for the advice… btw I would order the magazine, but I live in Hungary, so um… I’m left pretty much with you (which is I’m sure better than that magazine :slight_smile: )
Take Care

I figured it out
here it is

set theDate to current date
copy theDate to b
set month of b to January
set theDay to text -2 thru -1 of ("0" & theDate's day)
set month of b to January
set theMonth to text -2 thru -1 of ("0" & (1 + (theDate - b + 1314864) div 2629728))
set theYear to text -2 thru -1 of ((year of theDate) as text)
set theShortDate to theYear & theMonth & theDay

return theShortDate

should anyone need it… there must be a more simple way, but this works fine for me :slight_smile: