As part of a larger script I’m setting the value of two cells in a sheet i of a workbook “reducedDataName” and then taking the calculations resulting from that and putting them into another sheet in a workbook “combinedDataName”. Everything works great until it comes time to copy the cells into “combinedDataName”. It finds the right cells and columns, targets the correct data…and then sets the data into the active sheet of “reducedDataName” rather than into the sheet of “combinedDataName” where I want it.
Here is the code snippet
set value of cell "R5" of sheet i to natalStart
set value of cell "S5" of sheet i to natalEnd
tell range "R7:U7" of sheet i of reducedDataName
set value of (get resize (row otoRow of column natalData of sheet otoData of combinedDataName) ¬
row size (count rows) column size (count columns)) to get its value
end tell
Here is the Event Log output for that section of code when I run it
get value of range "R7:U7" of sheet 14 of workbook "Reduced data (test).xlsx"
{{0.890309580752, 0.166119278667, 0.011748607105, 0.01535434176}}
count every row of range "R7:U7" of sheet 14 of workbook "Reduced data (test).xlsx"
1
count every column of range "R7:U7" of sheet 14 of workbook "Reduced data (test).xlsx"
4
get worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
get worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
get resize row 28 of column 15 of sheet (worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx") of workbook "Combined Datasheet (test).xlsx" row size 1 column size 4
range "'[Reduced data (test).xlsx]4102'!$O$28:$R$28"
set value of range "'[Reduced data (test).xlsx]4102'!$O$28:$R$28" to {{0.890309580752, 0.166119278667, 0.011748607105, 0.01535434176}}
It looks like I’m not referencing the range correctly and it is automatically using the range for the active sheet. I’m not sure why I’m not getting this, it seems like it should be pretty straightforward.
Your help would be greatly appreciated. I’ve been beating my head on this for too long.
Perhaps I should have been more specific. You are right, the first two set value statements are not fully qualified and I’ll fix them to avoid issues in the future. They are working though.
The problem is in the tell statement where I am trying to set range values in workbook combinedDataName using values from workbook reducedDataName. I think that statement is fully qualified but it isn’t setting the range in the right workbook.
@bills - I tried adding an activate statement but it didn’t do anything
set value of (get resize (row otoRow of column natalData of sheet otoData of combinedDataName) ¬
row size (count rows) column size (count columns)) to get value of (range "R7:U7" of sheet i of reducedDataName)
although you might try
tell range "R7:U7" of sheet i of reducedDataName
set value of (get resize (row otoRow of column natalData of sheet otoData of combinedDataName) ¬
row size (count rows) column size (count columns)) to value
end tell
Hmm…I’m confused now. I tried both of your ideas mikeerickson. The first one seems to work better than the other. According to the Event Log it is trying to put it in the right place now (in sheet “Otolith Data” of worksheet "Combined Datasheet (test).xlsx)…but it’s still defaulting to the active sheet rather than to setting the values it where it says it is putting it.
So, this seems like a step in the right direction but it’s still not getting through. Any ideas?
Here is the code I have currently
set value of cell "R5" of sheet i to natalStart
set value of cell "S5" of sheet i to natalEnd
set value of (row otoRow of column "O:R" of sheet otoData of combinedDataName) ¬
to get value of (range "R7:U7" of sheet i of reducedDataName)
Here is the Event Log output
set value of cell "R5" of sheet 14 to 100.0
set value of cell "S5" of sheet 14 to 200.0
get value of range "R7:U7" of sheet 14 of workbook "Reduced data (test).xlsx"
{{0.890309580752, 0.166119278667, 0.011748607105, 0.01535434176}}
get worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx"
set value of row 28 of column "O:R" of sheet (worksheet "Otolith Data" of workbook "Combined Datasheet (test).xlsx") of workbook "Combined Datasheet (test).xlsx" to {{0.890309580752, 0.166119278667, 0.011748607105, 0.01535434176}}
Sorry for the delay, I was out doing fieldwork today.
Here is where both reducedDataName and combinedDataName are set. The files are intially set from a dialog, but that gives you a file path. I then set reducedDataName and combinedDataName as the name of those files.
--sets which file contains the reduced data
set reducedData to choose file with prompt "Choose file containing the reduced data." & return & return & "(Element data will be copied FROM this file)"
--sets which file contains the combined datasheet
set combinedData to choose file with prompt "Choose the combined data file" & return & return & "(Element data will be copied TO this file)"
--opens chosen files
tell application "Microsoft Excel"
activate
open combinedData
set combinedDataName to workbook (name of active workbook)
open reducedData
set reducedDataName to workbook (name of active workbook)
I suppose closing one file and leaving only one active sheet might work, but I’m iterating this for several hundred data points. Seems inefficient to have Excel opening and closing a file over and over again.
Try adding " workbook" before combindedDataName and ReducedDataName
s
et value of (row otoRow of column "O:R" of sheet otoData of workbook combinedDataName) ¬
to get value of (range "R7:U7" of sheet i of workbook reducedDataName)
tell application "Microsoft Excel"
activate object worksheet i of workbook reducedDataName
set value of cell "R5" of sheet i to natalStart
set value of cell "S5" of sheet i to natalEnd
set gotvalue to get value of range "R7:U7"
activate object worksheet otoData of workbook combinedDataName
set value of row otoRow of column "O:R" to gotvalue
end tell
bills you have done the trick! I owe you one, this script has been a thorn in the side of my masters thesis for way to long. One of those things that I keep getting frustrated with and setting aside until I couldn’t set it aside any longer. Your tweak made it work though.
I guess I was under the impression that you could just say “activate xxx” but apparently I needed to make it “activate object xxx”. I did have to take out the workbook term since both combinedDataName and reducedDataName are defined as a workbook already. The double reference was screwing it up. Also I had to abandon setting the worksheet to the otoData variable and reference it directly, that threw an error too. But it works now…whew!!
Here is the final working code for posterity:
tell application "Microsoft Excel"
activate object worksheet i of reducedDataName
set value of cell "R5" of sheet i to natalRingStart
set value of cell "S5" of sheet i to natalRingEnd
set gotvalue to get value of range "R7:U7"
activate object worksheet "Otolith Data" of combinedDataName
set value of row otoRow of column "O:R" to gotvalue
end tell