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