I have another problem that I don’t have a starting point for and I was wondering if someone could point me in the right direction.
I have a workbook that I use for invoicing purposes and I want to automate transferring several values from the invoices into a sales log, set up as a separate workbook, whereby each sheet has entries for a different month, i.e. the sheets are named Jun 09, Jul 09, Aug 09, etc.
I am fine with transferring the relevant data but what I need help with is automatically identifying which sheet to post the information on and creating new sheets (using the same date format where necessary) where applicable.
The current date is displayed on the invoice in cell G26 - I’m not even sure if this is relevant?!?!?!
I’d be very grateful for any help.
Not sure if I made myself completely clear - I want to use the date on the invoice to post information to the corresponding sheet for that month (in another workbook).
Any help greatly appreciated!
Hi,
this is a snippet to start with. It reads the values of cell G26 of all sheets and displays the results
tell application "Microsoft Excel"
tell active workbook
repeat with oneSheet in (get sheets)
set sheetName to name of oneSheet
set invoice to value of range "G26" of oneSheet
-- do something
display dialog "name: " & sheetName & return & "value of G26: " & invoice
end repeat
end tell
end tell
Perhaps somehthing like this
tell application “Microsoft Excel”
set aDate to get value of range “g26” of sheet “Invoice”
set monthStr to ((month of aDate) as text)
set yearStr to ((year of aDate) as text)
set matchingSheetName to (characters 1 thru 3 of monthStr as string) & " " & (characters 3 thru 4 of yearStr as string)
set matchingSheet to worksheet matchingSheetName of active workbook
set value of range "a2" of matchingSheet to "x"
end tell