I apologize for the length of this but I would really appreciate it if any of you could take the time to read through it and hopefully offer some suggestions or actual solutions.
What i am specifically hoping for is some help with the automation of Excel, but I’m including the whole process because, really, you’re a lot better at it than I am : )
Everything SEEMS like it should be scriptable but i’m unsure about the part where i have to identify the cells that need to be moved and then move them (items 8-15) i hope that bit can be scripted, but i don’t know enough about applescript to even know where to start on that one.
I can provide the PDF, the resultant text file, and my two bbedit scripts if necessary.
What I am hoping to come up with when all is said and done is a script that can be run as either a folder action or a droplet.
Here are the steps involved:
1.) take a file with a name format of MMDDYY_TIMESEQUENCE.pdf (with MMDDYY being the next day’s date) and open it in Acrobat Pro
2.) export that file as Text (Plain) using UTF-8
3.) open the resulting text file in BBedit and run a script.
4.) open the file in excel as a delimited file (using | as the delimiter)
5.) sort by row 1 (select the range from A1:AE300 first?)
6.) select the range from A8:AE300
7.) sort that selection by column P, ascending
8.) select the portions of column P that start with a letter
9.) move the selection one column to the right (Q) and extend it to include Columns R & S
10.) move the data from columns Q,R,S to P,Q,R overwriting the data in column P
11.) select the range from A8:AE300
12.) sort that selection by column T, ascending
13.) select the portions of column T that start with a letter
14.) move the selection one column to the right (U) and extend it to include Columns V & W
15.) move the data from columns U,V,W to T,U,V overwriting the data in column T
16.) Delete Columns X,Y,Z,AA,AB,AC,AD,AE
17.) Save as Tab Separated Text and select “Yes” (default) on dialog that pops up
18.) close file and select “don’t save” on dialog that pops up
19.) open the file in BBedit and run a script
20.) open Mail.app and attach the file
One slight modification might be possible in number 8 and 13 in that the rows that start with letters in columns P/T will start with numbers in columns Q/U (while the cells that aren’t being moved in that column start with letters)
One other question… is it possible to replace a text string such as {tomorrow’s date} in BBedit with the actual following day’s date in the format MM/DD/YY?
Thank you very much for any help you might be able to give me on this. It’s not a complicated task, but being able to automate it will make my life much much much more pleasant (it has to be done between 7pm-9pm and I have many things I’d rather be doing during those hours).
-david