Paste Table created outside of Excel into Excel

Previous help from this forum resulted in working on data out side of excel. I have run into an issue of putting the data created back into an Excel worksheet…

the code is below: in essence what it does copy an existing worksheet into a string “AllNewStocks” it then checks to see if any of those stocks already exist in another worksheet. It creates a further string “TransferStocks” with any new stocks.

What I cannot figure out is how to post that string into the workbook, the copy range transfer stocks fails.


--Create a synthetic Data Base for Existing & New stocks
set TransferStocks to {}
set Trns to 0
tell application "Microsoft Excel"
	--Get Existing stocks
	set WSName to "OptionDataResults"
	set {LCell, LRow} to my WSDimension(WSName)
	set LRowS to LRow + 1
	set StartRowNew to "A" & LRowS --1st Empty row in "OptionDataResults"
	log StartRowNew
	set AllExistingStocks to string value of range ("A3:" & LCell)
	set CountExistingStocks to count of AllExistingStocks
	--Get New Stocks
	set WSName to (name of last worksheet of active workbook)
	set {LCell, LRow} to my WSDimension(WSName)
	set AllNewStocks to string value of range ("A2:" & LCell)
	set CountNewStocks to count of AllNewStocks
end tell
--Cycle through All new stocks
repeat with StockCheck from CountNewStocks to 1 by -1
	set NewStock to item 1 of item StockCheck of AllNewStocks --Stock Symbol
	--Cycle through all stocks allready in the database
	repeat with ExistCheck from CountExistingStocks to 1 by -1 --All Stocks in Database
		if (item 1 of item ExistCheck of AllExistingStocks) is NewStock then set Trns to 1 --1st Item is the symbol "true" then  Already in Database
	end repeat
	--Build database of stocks to be transferred
	if Trns is not 1 then --To be added to Database
		set end of TransferStocks to item StockCheck of AllNewStocks
		log TransferStocks
		set Trns to 0
	end if
	set Trns to 0
end repeat
log (count of TransferStocks) --To get number of rows

--Set Start address: 1st empty position on WS "OptionDataResults = "A" +LRowS
--set End address: LRowS+ count of transfer stocks. Last column is "H"
log "Start " & "A" & LRowS & " End " & "H" & LRowS + (count of TransferStocks)
tell application "Microsoft Excel"
	select worksheet "Optiondataresults"
	copy range TransferStocks 
	paste special range {"A" & LRowS, ("H" & LRowS + (count of TransferStocks))}
end tell
--Set Transfer from the last worksheet to worksheet  "OptionDataResults"
on WSDimension(WSName) --Get Last cell for Existing & New stocks
	tell application "Microsoft Excel"
		select worksheet WSName
		set LRow to first row index of (get end (last cell of column 1) direction toward the top)
		set LCol to count of columns of used range of active sheet
		set LCell to get address of (row LRow of column LCol)
	end tell
	return {LCell, LRow}
end WSDimension


I’m not sure what you’re trying to accomplish here. Your script errors out for me, also at the ‘copy range TransferStocks’ line. Of course, I’m working from a blank workbook/worksheet (although I did rename the single sheet to “OptionDataResults”. If you could provide some appropriate data it might make things easier.

By the way, TransferStocks doesn’t seem to be a range so ‘copy range TransferStocks’ isn’t going to work.

Thanks for getting back to me. I did manage to solve my problem, I had not thought it through properly. For what its worth below is the script that works, thanks again. You were of course correct about “TransferStocks” and the change is how I fixed it “set value of range (“A” & LRowS & “:” & (“H” & LRowS + (count of TransferStocks) - 1)) to TransferStocks

--Add any Stocks not in Database to worksheet "OptionDataResults"
my NewStockstoOptionDataResults()

on NewStockstoOptionDataResults()
	--Create a synthetic Data Base for Existing & New stocks
	set {TransferStocks, Trns} to {{}, 0}
	tell application "Microsoft Excel"
		--Get Existing stocks
		set WSName to "OptionDataResults"
		set {LCell, LRow} to my WSDimension(WSName)
		set LRowS to LRow + 1
		set StartRowNew to "A" & LRowS --1st Empty row in "OptionDataResults"
		set AllExistingStocks to string value of range ("A3:" & LCell)
		set CountExistingStocks to count of AllExistingStocks
		--Get New Stocks
		set WSName to (name of last worksheet of active workbook)
		set {LCell, LRow} to my WSDimension(WSName)
		set AllNewStocks to string value of range ("A2:" & LCell)
		set CountNewStocks to count of AllNewStocks
	end tell
	--Cycle through All new stocks
	repeat with StockCheck from CountNewStocks to 1 by -1
		set NewStock to item 1 of item StockCheck of AllNewStocks --Stock Symbol
		--Cycle through all stocks allready in the database
		repeat with ExistCheck from CountExistingStocks to 1 by -1 --All Stocks in Database
			if (item 1 of item ExistCheck of AllExistingStocks) is NewStock then set Trns to 1 --1st Item is the symbol "true" then  Already in Database
		end repeat
		--Build database of stocks to be transferred
		if Trns is not 1 then --To be added to Database
			set end of TransferStocks to item StockCheck of AllNewStocks
			set Trns to 0
		end if
		set Trns to 0
	end repeat
	--Set Start address: 1st empty position on WS "OptionDataResults = "A" +LRowS
	--set End address: LRowS+ count of transfer stocks. Last column is "H"
	tell application "Microsoft Excel"
		select worksheet "Optiondataresults"
		set value of range ("A" & LRowS & ":" & ("H" & LRowS + (count of TransferStocks) - 1)) to TransferStocks
		tell active sheet to clear range formats used range
		set number format of column "F:F" to "0.00%"
		set number format of column "G:H" to "$###.00"
		set number format of column "E:E" to "yy/mm/dd"
	end tell
end NewStockstoOptionDataResults

--Set Transfer from the last worksheet to worksheet  "OptionDataResults"
on WSDimension(WSName) --Get Last cell for Existing & New stocks
	tell application "Microsoft Excel"
		select worksheet WSName
		set LRow to first row index of (get end (last cell of column 1) direction toward the top)
		set LCol to count of columns of used range of active sheet
		set LCell to get address of (row LRow of column LCol)
	end tell
	return {LCell, LRow}
end WSDimension