put newList into excel

Hi
I’ve been trying all day to put the new list to replace the old list in excel.Is there a way to reverse the way I got the list?
I tried delete old list and repeat to do the cells of the new list, but it is very slow if the lists are in the thousands
example old list
1 2 3
2 3 4
3 4 5
4 5 6
5 6 7
6 7 8
7 8 9
8 9 10
9 10 11
10 11 12
11 12 13
need new list for repeat.

tell application "Microsoft Excel"
	activate object worksheet "Sheet1"
	activate object range "A1"
	select current region of active cell
	tell window 1
		set SelectedCells to (value of selection as list)
	end tell
	set oldList to SelectedCells
	set NewList to (items 1 thru 3 of oldList)
	
end tell

thanks
bills

Your script script crashes my Excel…

To figure out what structure your lists might have I made a new workbook, and typed 1-9 into cells A1-C3.
Then I ran this simple script:

tell application "Microsoft Excel"
	value of range "A1:C3"
end tell
--> {{1.0, 2.0, 3.0}, {4.0, 5.0, 6.0}, {7.0, 8.0, 9.0}}

So, to replace values in a block of cells:

tell application "Microsoft Excel"
	set value of range "A1:C3" to {{1.0, 2.0, 3.0}, {4.0, 5.0, 6.0}, {7.0, 8.0, 9.0}}
end tell

Basic solution finding, my dear Watson :wink:

You can find the Excel AppleScript Reference here.

Hi
thanks alastor, That should speed things up in a lot of scrips.Downloaded your reference. Should help in the future.
thanks
bills

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

Thanks Tim
I think that script will come in handy.
bills