I feel like I have seen this here before but I have been searching and can’t find it.
I want to take a spreadsheet with an unknown number of data rows and columns and get the range of just the used cells. I was looking at the “Special Cells” in the reference and “current region” in the dictionary but can’t quite figure out how to use them.
Anyone already have code to do this?
Model: PowerPC G5 Tower OS 10.4.8
AppleScript: XCode 2.5
Browser: Safari 419.3
Operating System: Mac OS X (10.4)
Hi Matt-Boy,
Does this help you any
This will find the next empty cell, could you get what you want once you know that?
tell application "Microsoft Excel"
activate
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
Regards,
Nick
Thanks for the reply. I your script on a test file. The results looks llike this:
range “[test.xls]Sheet1!$A$48” of application “Microsoft Excel”
I tried this on a sheet that had 7 columns and 47 rows. It seems to have found the last row but not the last column.
My other concern is that if value in the A column on any of those rows is blank, it will stop, thinking that it’s the last line.
Basically, I am trying to determine the “width” and “height” of all the used cells so I can extract everything into another format. I want to repeat through all the rows that have values and know how many times I have to repeat through each column to pull the data out.
I hope that’s more clear.
Thanks.
Hi,
(active) sheet in Excel has a used range element,
which includes all cells of the imaginary rectangle around the maximum used row and column
tell application "Microsoft Excel"
tell used range of active sheet to set {maxRows, maxColumns} to {count rows, count columns}
end tell
Thanks for the explanation Matt.
Usually when I’m doing a script that utilises Excel I more often than not pass the range required into the relevant handler.
So I pull out the data something like this:-
set list1 to value of every cell of range (thisCol & startRow & ":" & thisCol & endRow)
Is that not an option?
The only slight problem I had with this method is that I had to pull out each column separately i.e. list1, list2, list3, list4.
I’m still looking for a way to pass in the list name into a var or generate it on the fly.
Apart from that slight niggle everything else worked fine.
I had in mind a nested repeat loop thing where I could go through every column of every row one cell at a time so I think Stefan’s solution will work best for me.
tell application "Microsoft Excel"
tell used range of active sheet to set {maxRows, maxColumns} to {count rows, count columns}
repeat with rowCounter from 1 to maxRows
repeat with colCounter from 1 to maxColumns
--process cell rowCounter of column colCounter
end repeat
end repeat
end tell
Thanks, guys. This is why I love this forum.
-Matt
In case anyone cares, this is what I was experimenting with. This will take an Excel spreadsheet and, assuming it has row 1 as the row with header names, pull all of the rows out and create a SQL database. I added a subroutine that gets rid of single quotes in the data as that messes up the shell script and I also put in code that I found on this site somewhere to strip out leading and trailing spaces and line breaks. This script assumes you have the spreadsheet open and the worksheet you want it the active sheet.
set theHeaderList to {}
tell application "Microsoft Excel"
tell used range of active sheet to set {maxRows, maxColumns} to {count rows, count columns}
repeat with colCounter from 1 to maxColumns
set theHeaderList to theHeaderList & (value of cell 1 of column colCounter)
end repeat
end tell
set AppleScript's text item delimiters to ","
set theHeaderList to theHeaderList as text
set AppleScript's text item delimiters to ""
set PathToDesktop to POSIX path of (path to desktop)
set theDBName to the text returned of (display dialog "Name the new SQL database:" default answer "NewName.db")
set theTableName to the text returned of (display dialog "What should the main table be named?" default answer "NEW_TABLE")
set theFileNameShell to "sqlite3 " & PathToDesktop & theDBName
(do shell script theFileNameShell)
(do shell script theFileNameShell & " " & quote & "create table " & theTableName & "(" & theHeaderList & ");" & quote)
tell application "Microsoft Excel"
repeat with rowCounter from 2 to maxRows
set thisRowList to {}
repeat with colCounter from 1 to maxColumns
set thisRowList to thisRowList & (my stripOutSingleQuote((string value of cell rowCounter of column colCounter) as text))
end repeat
set AppleScript's text item delimiters to "', '"
set thisRowList to (("'" & thisRowList as text) & "'")
set AppleScript's text item delimiters to ""
set theShell to (theFileNameShell & " " & quote & "insert into " & theTableName & " values(" & thisRowList & ");" & quote)
do shell script theShell
end repeat
end tell
on stripOutSingleQuote(theVar)
repeat until (theVar does not start with " ") and (theVar does not start with return)
set theVar to text 2 thru -1 of theVar
end repeat
repeat until (theVar does not end with " ") and (theVar does not end with return)
set theVar to text 1 thru -2 of theVar
end repeat
set AppleScript's text item delimiters to "'"
set theVartemp to theVar's text items
set AppleScript's text item delimiters to "<SingleQuote>"
set theVartemp2 to theVartemp as text
set AppleScript's text item delimiters to ""
return theVartemp2
end stripOutSingleQuote
Model: PowerPC G5 Tower OS 10.4.9
AppleScript: XCode 2.5
Browser: Safari 419.3
Operating System: Mac OS X (10.4)
Thanks for posting that Matt 
Only really worked with SQL when I was doing Coldfusion work a few years ago now, so it’s quite interesting seeing something that involves AS and SQL.
Regards,
Nick