Hi Bills,
Here is a script for getting excel worksheet contents into a list and back out again to excel. It is broken into handlers so it can be easily modified. There are some dialog boxes for choosing files and sheets to read and write to. I hope it helps.
--SCRIPT TO READ DATA FROM AN OPEN EXCEL FILE TO AN APPLESCRIPT LIST AND RETURN IT TO ANOTHER EXCEL FILE--
set theWkbookInfo to my start_excelTargetReadDialog() --returns the name of the sheet and workbook with which we want to work
set theWorkbook to item 1 of theWkbookInfo --the workbook reference
set theWorksheet to item 2 of theWkbookInfo --the worksheet reference
--:to get the data in theWorksheet contained by theWorkbook
--:find out the range in use within the worksheet, i.e. the size of our table,
--:for simplicity we test here ONLY on the first column and first row.
set rangeInUse to my excel_readRangeInUse(theWorkbook, theWorksheet) --returns eg.{rows,cols} eg. {1, 1}
-->log "the spreadsheet has a total range in use of: " & item 1 of rangeInUse & " x " & item 2 of rangeInUse
-->example:(*the spreadsheet has a total range in use of: 5 x 4*)
--::If the sheet is empty: Do nothing
if rangeInUse is {1, 1} then --ie. the sheet is empty
tell application "System Events"
activate
display dialog "The Worksheet you chose appears to be empty.\rThe first column and row should not be blank.\r Please modify this sheet or choose another file and try again."
error number -128
end tell
end if
--::If the sheet contains data,read it.
if rangeInUse ≠{1, 1} then --the sheet contains data
--Getting the various sheet references (ranges) for the Headers and the Data
set theHeadingRange to excel_getHeaderRange(theWorkbook, theWorksheet) --eg.range "[Workbook2]Sheet1!$A$1:$D$1"
set theBodyRange to excel_getBodyRange(theWorkbook, theWorksheet) --eg.range "[Workbook2]Sheet1!$A$2:$D$5"
tell application "Microsoft Excel"
set the theHeadingData to the value of theHeadingRange
--log "the HeaderData has been set to the value of the headerRange which is: " & HeaderData
--> {{"DATE", "ITEM", "COLOR", "ACTION"}}
set the theHeaderData to item 1 of the theHeadingData --because its a list within a list, as its a row of excel data
--log "HeaderData has now been set to the value of item 1 of HeaderData which is: " & HeaderData
set the theExcelData to the value of theBodyRange
-->{{date "Wednesday, 21 January 2015 00:00:00", "plane", "red", "flies"}, {date "Thursday, 22 January 2015 00:00:00", "car", "orange", "drives"}, {date "Friday, 23 January 2015 00:00:00", "cycle", "yellow", "wheels"}, {date "Saturday, 24 January 2015 00:00:00", "pedalo", "blue", "pedals"}}
-->note each excel row is one inner list.
end tell
end if --> we now have acquired the data and its stored in the list 'theExcelData'
------------------------------------
--do some stuff with the data here
--do stuff here
--do stuff here
--do stuff here
--do stuff here
--do stuff here
------------------------------------
--:TO WRITE THE NEW DATA BACK TO EXCEL
--calculate the range of the data
set theNewHeaderData to theHeaderData
set theExcelDataToWrite to theExcelData
set theNumberOfColumns to count of items of theNewHeaderData
set theNumberOfRows to count of items of theExcelDataToWrite
--to write the data to an excel file
set theTargetInfo to my excel_writeTargetDialog()
set theWorkbook to item 1 of theTargetInfo
set theWorksheet to item 2 of theTargetInfo
tell application "Microsoft Excel"
tell workbook theWorkbook
tell worksheet theWorksheet
activate
--Write: the Headers to the Header range (which is a single row)
set theHeaderRowNumber to 1
set theNumberOfHeaderRows to 1
set theNewHeaderRange to (get resize range ("A" & theHeaderRowNumber & ":" & "A" & theHeaderRowNumber) row size theNumberOfHeaderRows column size theNumberOfColumns)
set value of theNewHeaderRange to theNewHeaderData
--Write: the new data to the data range
set theDataRowNumber to 2
set theNewDataRange to (get resize range ("A" & theDataRowNumber & ":" & "A" & theDataRowNumber) row size (theNumberOfRows) column size theNumberOfColumns)
set value of theNewDataRange to theExcelDataToWrite
end tell
end tell
end tell
--------------------------------------------
on start_excelTargetReadDialog() --starts a dialog so you can choose a workbook and sheet with which to work.The workbook must be already open, but if its collapsed in the dock (or minimized), it will raise it. It returns the workbook and worksheet name you choose.
tell application "Microsoft Excel"
set windowsOpen to get name of every window --find out which windows are running
end tell
tell application "System Events"
activate
set targetWindow to choose from list windowsOpen with prompt "Reading Data from Excel\r Choose a Workbook:" --choose an open workbook from the dialog list
if result is false then
error number -128 --"User canceled."
end if
end tell
--to get a workbook name making sure its not minimized:
tell application "Microsoft Excel"
set targetWorkbook to targetWindow as text --the existing workbook chosen is now active
--::If the chosen workbook is open, but collapsed/minimized, we need to call it up:
set windowsOpen to get name of every window
--using a property {"collapsed"} of window (targetWorkbook)
try
set windowState to collapsed of window (targetWorkbook) --see if window is minimized
if windowState is true then
set collapsed of window (targetWorkbook) to false --un-minimize it..
end if
end try
--:select a sheet within the chosen workbook:
tell workbook targetWorkbook
set listWorksheets to get name of every worksheet
tell application "System Events"
activate
set targetSheet to choose from list listWorksheets with prompt "Using workbook:\r" & targetWorkbook & "\r Choose a sheet to get its contents:"
if result is false then
error number -128 --"User canceled."
end if
end tell
select worksheet (targetSheet as text)
activate
end tell
--end tell
end tell
--:now we return the name of the sheet and workbook with which we want to work.
tell application "Microsoft Excel"
return {name of active workbook, name of active sheet}
end tell
end start_excelTargetReadDialog
on excel_readRangeInUse(theWorkbook, theWorksheet)
tell application "Microsoft Excel"
tell workbook theWorkbook
tell worksheet theWorksheet
set startRow to get end range ("A" & (count rows)) direction toward the top
set rowsUsed to first row index of startRow --eg (row) 101
set columnsUsed to first column index of (get end (cell (count columns) of row 1) direction toward the left)
return {rowsUsed, columnsUsed}
end tell
end tell
end tell
end excel_readRangeInUse
on excel_getHeaderRange(theWorkbook, theWorksheet) --returns the range being used
tell application "Microsoft Excel"
tell workbook theWorkbook
tell worksheet theWorksheet
--::Rows
set startRow to get end range ("A" & (count rows)) direction toward the top
set rowsUsed to 1 --must not be zero
log "the excel_getHeaderRange rows used are: " & rowsUsed
--::Columns
set lastCol to get end (cell (count columns) of row 1) direction toward the left --returns a range eg 'R1'
set colsUsed to (first column index of lastCol) --returns an integer eg (col) 18
log "the excel_getHeaderRange columns used are: " & colsUsed
set headerTest to {rowsUsed, colsUsed}
if headerTest is {1, 1} then
tell application "System Events"
activate
display dialog "The Worksheet you chose appears to be empty.\rThe first column and row should not be blank.\r Please modify this sheet or choose another file and try again."
error number -128
end tell
end if
--::Resize Range
set theRange to (get resize range ("A1:A1") row size rowsUsed column size colsUsed)
return theRange --returns a range
end tell
end tell
end tell
end excel_getHeaderRange
on excel_getBodyRange(theWorkbook, theWorksheet) --returns the range being used
tell application "Microsoft Excel"
tell workbook theWorkbook
tell worksheet theWorksheet
--::Rows
set startRow to get end range ("A" & (count rows)) direction toward the top
set rowsUsed to (first row index of startRow) --eg (row) 101
set rowsUsed to (first row index of startRow) - 1 --we remove one row because of the header row
log "the excel_getBodyRange rows used are: " & rowsUsed
if rowsUsed = 0 then
tell application "System Events"
activate
display dialog "The Worksheet you chose appears to be empty.\rThe first column and row should not be blank.\r Please modify this sheet or choose another file and try again."
error number -128
end tell
end if
--::Columns
set lastCol to get end (cell (count columns) of row 1) direction toward the left --returns a range eg R1
set colsUsed to (first column index of lastCol) --returns an integer eg (col) 18
log "the excel_getBodyRange columns used are: " & colsUsed
if colsUsed = 0 then
tell application "System Events"
activate
display dialog "The Worksheet you chose appears to be empty.\rThe first column and row should not be blank.\r Please modify this sheet or choose another file and try again."
error number -128
end tell
end if
--::Resize Range
set theRange to (get resize range ("A2:A2") row size rowsUsed column size colsUsed)
-- set theRange to (get resize range ("A1" & ":" & "A" & rowsUsed) row size rowsUsed column size colsUsed)
return theRange --returns a range
end tell
end tell
end tell
end excel_getBodyRange
on excel_writeTargetDialog()
set writeDestination to {"", ""} --holds the workbook and worksheet names to be returned
set createNewSheetName to "ScriptOutput Sheet"
tell application "Microsoft Excel"
try
set windowsOpen to get name of every window --find out which windows are running
set end of windowsOpen to "Create a new output book" --create a new option
on error
display dialog "You must have one workbook open"
return
end try
end tell
tell application "System Events"
activate
set targetWindow to choose from list windowsOpen with prompt "Writing Data to Excel\r Choose an output Workbook:" --create a list
if result is false then --"User canceled."
error number -128 --stop the script
end if
end tell
--If you chose to create a new workbook:
if targetWindow as text = "Create a new output book" then
tell application "Microsoft Excel"
set targetWorkbook to make new workbook
--> workbook "Sheet3"
set name of active sheet to createNewSheetName
-->set name of active sheet to "ScriptOutput Sheet"
select worksheet createNewSheetName --The new workbook and this sheet are now active
activate
end tell
set writeDestination to {targetWorkbook, createNewSheetName}
else
--If you chose to not create a new workbook, but selected an existing open one:
tell application "Microsoft Excel"
set targetWorkbook to targetWindow as text
--:First, if the chosen workbook is minimized we need to raise it or wake it up:
set windowsOpen to get name of every window
--note the use of property {"collapsed"} of window (targetWorkbook)
try
set windowState to collapsed of window (targetWorkbook) --see if window is minimized
if windowState is true then
set collapsed of window (targetWorkbook) to false --un-minimize it..
end if
end try
--:Second,we need to see which sheets the workbook contains:
tell workbook targetWorkbook
set listWorksheets to get name of every worksheet
set end of listWorksheets to "Insert a new output sheet"
end tell
tell application "System Events"
activate
set targetSheet to choose from list listWorksheets with prompt "Writing Data to workbook:\r" & (targetWorkbook) & "\rChoose an output sheet"
if result is false then
error number -128 --"User canceled."
end if
end tell
--:now either create and insert a new worksheet:
if item 1 of targetSheet is "Insert a new output sheet" then
tell application "Microsoft Excel"
tell workbook targetWorkbook
make new worksheet at beginning with properties {name:createNewSheetName}
set targetSheet to createNewSheetName --new name
--activate
end tell
end tell
else
--:or activate the selected existing sheet
log "targetSheet is: " & (item 1 of targetSheet)
--return targetSheet
tell application "Microsoft Excel"
tell workbook targetWorkbook
activate object worksheet (item 1 of targetSheet)
end tell
end tell
end if
end tell
end if
tell application "Microsoft Excel"
set targetWorkbook to name of active workbook
set targetWorksheet to name of active sheet
set writeDestination to {targetWorkbook, targetWorksheet}
end tell
return writeDestination
end excel_writeTargetDialog