Excel 2008 scripting problem

Scripting Forums AppleScript | Mac OS X
,
#1

Hi,

I wonder if any of you can help; Applescript is all new to me and I am trying to simply automate a spreadsheet I have set up to automatically update a product inventory when I run the script.

The basic task is simple enough. I have a column “R” that has the current inventory recorded and column “S” which holds the figures for the adjustments to be made to the inventory. I have written a very basic piece of script that will perform this simple function for the first row of figures in the columns only. What I need to be able to do is to perform the same function for all subsequent rows of products, i.e. I want to repeat it until there are no more products listed, which would mean there would be no product code in row “J”.

Here’s what I have so far:

local currentInventory, inventoryAdjustment, newInventory

tell application "Microsoft Excel"
	
	set currentInventory to value of cell "R2"
	set inventoryAdjustment to value of cell "S2"
	set newInventory to currentInventory - inventoryAdjustment
	calculate newInventory
	set value of cell "R2" to newInventory
	
end tell

Any help would be greatly appreciated, I have tried searching through the forums but have been a bit overwhealmed by how little I understand and the quantity of posts regarding excel 2008.

Thanks.

#2

This should do what you want.

tell application "Microsoft Excel"
	tell column 20 of entire row of current region of range "J2"
		insert into range (get resize column size 2) shift shift to right
		set formula to "=R1-S1"
		set value to get value
		tell (get resize (get offset column offset -2) column size 2)
			delete range shift shift to left
		end tell
	end tell
	
end tell
#3

Thanks for your help mikerickson, although this script isn’t quite working for me - I just wish I understood more of it, so that I knew how to fix it!

When I run this script, everything in column “S” is being deleted (which is not what I want to do - I want to leave the data in this column as is). Also, although the correct values are calculated and displayed in column"R", the are not in the correct rows - 1st answer should appear in “R2” (because R1 is header row) but it appears in “R1”, and so on.

Thank you for your patience and your help.

#4

What the script is doing is inserting a helper column and using Excel to calculate the result.
This might match you need better

tell application "Microsoft Excel"
	tell column 20 of entire row of current region of range "J2"
		insert into range shift shift to right -- insert helper cells
		set formula to "=R2-S2" -- put formula in helper cells
		set value of (get offset column offset -2) to get value -- move value to column R
		delete range shift shift to left -- delete helper cells
	end tell
end tell
#5

Thanks again, I really do appreciate your patience and the notes you added to the script.

The altered script works much better, although the results are still moving into column “R”, into the 1st row, instead of leaving the header. Any ideas?

#6

I just had to refine the range it acts on

tell application "Microsoft Excel"
	tell column 20 of entire row of (get resize range "J2" row size (first row index of (get end (range "J65536") direction toward the top)) - 1)
		insert into range shift shift to right -- insert helper cells
		set formula to "=R2-S2" -- put formula in helper cells
		set value of (get offset column offset -2) to get value -- move value to column R
		delete range shift shift to left -- delete helper cells
	end tell
end tell
#7

Brilliant! It works perfectly! I can’t thank you enough.