I’ve got hundreds of pages to create from data stored in an Excel file. I’m an AppleScript newb.
I’m trying to figure out how to copy a row from Excel, open the html template file in BBEdit, search and replace based upon the columns from that row, save the file using one of the cell’s values as the file name.
So far I’ve got how to pull a row of data into some sort of array, but am at a loss as to the next steps.
tell application "Microsoft Excel"
set myVal to get value of cell "A1:D1" of worksheet "Page Names" of document "TEMP Pages.xls"
end tell
If you have Microsoft Office X, then for a newbie I would recommend one way to explore the topic is to use AppleScript’s “record” ability. Both Excel X and BBEdit are “recordable.”
Simple open the AppleScript Editor, hit record, and perform your tasks manually once through the cycle. This will not necessarily give you a fine-tuned and functional application, but it will likely give you the vast majority of the code you need to assemble exactly what you want. You’ll also likely learn alot in the process of that fine-tuning.
Mind you, not all Mac apps are recordable this way (which is really too bad, it’s such a great feature!), but you were lucky enough to be using the right two apps.
I recorded my actions, but i do not see the script interacting with Excel. It just recorded my cut from an excel cell and then the paste into BBEdit. Any tips on how to get this set-up. I’m also at a loss as how to then loop this so that it processes through each row of the excel spreadsheet.
tell application "BBEdit"
activate
open {file "tpp-template.html"} with LF translation
find "{{ProductName}}" searching in text 1 of text document "tpp-template.html" options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false} with selecting match
set selection of window 1 to "Focus Formula (50ml)"
select insertion point after selection of window 1
save text document "tpp-template.html" to file "focus-concentration-memory-attention.html"
close active document of window 1
end tell
Excel 2004 isn’t recordable, from what I understand. This one still hasn’t been explained to me…if “2004” is newer or older than the Excel that comes with Office X, so it’s still a source of confusion for me.
Once you get the first selection in Excel figured out, looping it will be straighforward since the selection will give you the format Excel wants for cells and rows. Unfortunately, I was told that the format for selecting cells and rows also changed slightly between Excel 2004 and Office X, so I hesitate to give any advice and send you down a dead-end again.
But at least you have a really good start on the BBEdit side of things.
I’ll have to leave you to more capable hands from here…I gotta get back to my real job.
Here is a script that will extract all the data within a defined section of an Excel 2004 sheet, and place each row of data in a list, then add that list to a master list, so that you end up with a list of lists that contain all the row data for the defined space.
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 2 --These are the rows you want
repeat with c_col from 1 to 7 --These are the columns you want
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
master_data
Craig, I know what you posted is exactly what I need, yet I’m not sure how to make complete sense of it. I guess I’m having trouble seeing how to pass a value from the “array (list of lists)” to BBedit. Here’s what I’m trying to get work:
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 2 --These are the rows you want
repeat with c_col from 1 to 7 --These are the columns you want
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
tell application "BBEdit"
activate
open {file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:tpp-template.html"} with LF translation
find "{{ProductName}}" searching in text 1 of text document "tpp-template.html" options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false} with selecting match
set selection of window 1 to item dummy of (item dummy) --paste into replace field of BBEdits' search and replace window
select insertion point after selection of window 1
save text document "cell-contents of a row" to file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:focus-concentration-memory-attention.html"
close active document of window 1
end tell
end repeat
end tell
master_data
No sweat. Consider this. You have a sheet with data in rows 1 and 2 that extends out 3 columns each. The data is r1c1 for the first row, first column, r1c2 for the first row, second column, etc. Using my script, the variable master_data would look like this:
master_data is a list that contains 2 lists, each list containing 3 items. To get the individual pieces of data, you would first loop through the variable master_data, and then loop through each item in the 3-item lists something like this:
repeat with a_List in master_data --loops through the list of lists
repeat with an_Item in a_List --loops through the items in each list
--do something with each list item**
end repeat
end repeat
At the asterisked point is where each individual piece of information from the Excel sheet would be worked with. In this example, the first value would be r1c1, then r1c2, then r1c3, then it would start all over again with the second list and go through r2c1, r2c2, and r2c3. You would need to be prepared to put those values where you need them in the BBEdit document when they came up in the loop.
Thanks. How do you actually reference each list item so that I can say r1c1 gets pasted now, r1c2 gets pasted now, etc.?
Trying this:
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 2 to 2 --These are the rows you want
repeat with c_col from 1 to 3 --These are the columns you want
copy value of cell c_col of row r_row of active sheet to the end of dummy
tell application "BBEdit"
activate
open {file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:tpp-template.html"} with LF translation
find "{{ProductSKU}}" searching in text 1 of text document "tpp-template.html" options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false} with selecting match
set selection of window 1 to item 1 of dummy
end tell
tell application "BBEdit"
activate
open {file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:tpp-template.html"} with LF translation
find "{{ProductName}}" searching in text 1 of text document "tpp-template.html" options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false} with selecting match
set selection of window 1 to item 2 of dummy
end tell
tell application "BBEdit"
activate
open {file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:tpp-template.html"} with LF translation
find "{{ProductDescription}}" searching in text 1 of text document "tpp-template.html" options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false} with selecting match
set selection of window 1 to item 3 of dummy
save text document "tpp-template.html" to file "Macintosh HD:Users:jaredhawkins:Desktop:Native Remedies:Jared's Items:Native Remedies Development:tpp:focus-concentration-memory-attention.html"
close active document of window 1
end tell
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
master_data
Errrrr, well, I don’t know. I don’t use BBEdit, but the code you are using won’t work, because it is inside the dummy generation loop, and the dummy list continues to change.
Let’s start over here. Exactly how many fields in each Excel table are you going after? If it is only 2 or 3 cells, there is certainly an easier way to do all this; I posted my script assuming that you were going after a lot of data. If not, the easiest way could be similar to what you have, but without the loops.
If you indeed have a lot of data to process from a sheet, it will be much more efficient to just go to Excell, get ALL the data, and then move onto to another block of script that then processes the data into the files using BBEdit in another loop format. That would be faster and more efficient than what you have tried so far.
OK, now we are getting somewhere. I agree with James; For a template that size, AppleScript will be much easier than BBEdit to create all the different documents. He is also better than I am at that sort of thing, so let me help extract the Excel data, and let him help you write the files, if he is willing.
Are the 4 columns of data that you need from Excel in consecutive columns? Are they truly columns 1 thru 4? If so, then use my script above to create the master_data list of data, like this:
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 300 --Cycles through 300 rows
repeat with c_col from 1 to 4 --Gets the data from columns 1 thru 4
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
If your data is NOT in consecutive rows, let me know which rows you want. Either way, once the master_data list is created, you can whip through the html stuff quickly and cleanly with James’ assistance.
Yes, the columns will be consecutive. However, if a few more columns get added, I see where to change that.
The html code snippet I included is not the entire page (I left out the nav, footer, etc. from the template). I don’t think that will make a difference, right?
It should not, but if you could post those portions, it will help. You should also indicate where the Excel data needs to be inserted, aw well as where you want all these files saved, and how you want to name them, that will greatly assist whomever can help with the replacement of data. The text functions AS uses are pretty fast and versatile. Once all the details are worked out, this thing should run in just a few seconds and do all the work for you.
<!-- InstanceBeginEditable name="HBX_code" --><!--COPYRIGHT 1997-2005 WEBSIDESTORY,INC. ALL RIGHTS RESERVED. U.S.PATENT No. 6,393,479B1. MORE INFO:http://websidestory.com/privacy-->
This shouldn’t be too hard, but a few more questions… You didn’t list Product SKU as a cell, but you don’t have it in the html template… is it being replaced as well?
The order 1-10 that you listed… is that the same cell order I would find these in I assume?
Okay here goes, I can’t test it too much but let me know if you have any problems
set templateFile to POSIX path of (choose file with prompt "Please select your template file") -- Choose your template html file
set createPath to POSIX path of (choose folder with prompt "Where should I create the product pages?")
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 300 --Cycles through 300 rows
repeat with c_col from 1 to 10 --Gets the data from columns 1 thru 10
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
repeat with dataSet in master_data
set {H1data, ProSKU, ProName, ProSize, ProDesc, ProRetPr, ProYourPr, ProPhoto, ProPageTl, ProFileNm} to dataSet
do shell script "cat " & (quoted form of templateFile) & " | " & ¬
"sed -e 's/{{H1Data}}/" & H1data & "/' | " & ¬
"sed -e 's/{{ProductSKU}}/" & ProSKU & "/' | " & ¬
"sed -e 's/{{ProductName}}/" & ProName & "/' | " & ¬
"sed -e 's/{{ProductSize}}/" & ProSize & "/' | " & ¬
"sed -e 's/{{ProductDescription}}/" & ProDesc & "/' | " & ¬
"sed -e 's/{{ProductRetailPrice}}/" & ProRetPr & "/' | " & ¬
"sed -e 's/{{ProductYourPrice}}/" & ProYourPr & "/' | " & ¬
"sed -e 's/{{ProductPhoto}}/" & ProPhoto & "/' | " & ¬
"sed -e 's/{{ProductPageTitle}}/" & ProPageTl & "/' >> " & (quoted form of (createPath & ProFileNm & ".html"))
end repeat
The Condensed version
set templateFile to POSIX path of (choose file with prompt "Please select your template file") -- Choose your template html file
set createPath to POSIX path of (choose folder with prompt "Where should I create the product pages?")
set replaceSet to {"{{H1Data}}", "{{ProductSKU}}", "{{ProductName}}", "{{ProductSize}}", "{{ProductDescription}}", "{{ProductRetailPrice}}", "{{ProductYourPrice}}", "{{ProductPhoto}}", "{{ProductPageTitle}}"}
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 300 --Cycles through 300 rows
repeat with c_col from 1 to 10 --Gets the data from columns 1 thru 10
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
repeat with dataSet in master_data
set sedStmt to "cat " & (quoted form of templateFile)
repeat with i from 1 to 9
set sedStmt to sedStmt & " | sed -e 's/" & item i of replaceSet & "/" & item i of dataSet & "/'"
end repeat
set sedStmt to sedStmt & " >> " & (quoted form of (createPath & (item 10 of dataSet) & ".html"))
do shell script sedStmt
end repeat
Yes, I need to add {{ProductSKU}} into the html.