I work for a publisher and need a better way to determine deadline dates than by manually checking the spreadsheet.
There exists a master Excel doc that has 25 or so work sheets.
Each worksheet represents a title and all the pertinent information.
The script I need:
For each art director (with 3 or 4 titles) the script will find their title worksheet and then find the entry for ship date. The headers are not clean so it will need to search a row called “Ship/Date.”
It needs to build a new list as follows:
Art Director’s name
Title: Ship Date: Days between:
Mag1 10/10/03
Mag 2 11/10/03
Mag1 12/5/03
etc.
Excel can determine the days between.
OS9.2.2
AS 1.6
Excel 2001
Does any of this make sense?
My script will need to:
Open doc with a choice
Drop down for Art director from list of 8
Find their worksheet titles in master doc
Make a new list in a new spreadsheet with their ship dates in the above format for each art director individually or for all at once.
Sorry for the complexity. I have never scripted Excel, only Quark.
Any suggestions to even get me started would be great.
this doesn’t really sound all that difficult if you break it into steps…
select the file
set theWorkbook to (choose file with prompt "Please select an Excel file")
2)Prompt for Art Dir. name
set ads to {"ad1","ad2","ad3","ad4","ad5","ad6","ad7","ad8"} as list
set theAD to choose from list ads
The next part becomes a little sketchy as I can’t picture your excel sheet. But basically you would create a loop in excel to go through the different sheets/cells you need to look at and get the data from them. Then popluate the new sheet with the data. Excel does allow you to record steps in the script editor, that will give you an idea of what some of the syntax for excel looks like.
I am having trouble with the repeat loop to open the worksheet inside the Excel doc.
There are several version numbers in the format (AA-v3) “AA” is the magazine, “v” is a
place holder for version, and “3” is the version number. Some titles have 2 some have
11. It varys with each one, so I need the repeat loop to go through several trys.
Here is what I have so far:
set ADList to “DF-v” --This will be a multiple list which I will enclose in a repeat loop
tell application “Microsoft Excel”
Activate
set v to 1
try
Select Sheet (ADList & v)
on error
set v to v + 1
end try
end repeat
end tell
–I need it to try until the selected sheet is true or something like that
–What I have here continues to infinity
Any ideas? I looked at the example from the AS Language Guide but just can not get my
brain to convert what it says to what I need.
Any help or suggestions would be wonderful.
I always receive excellent suggestions from this site and look forward to any and all input.