Posted: 8 Jun 2020 16:56 EDT Last activity: 10 Jun 2020 8:35 EDT
How to Set Excel Values into Word Bookmarks
I have an Excel spreadsheet that will have multiple rows (possibly hundreds) of data (11 columns, with x rows). I will need to extract/copy each row/column data into a Word document that will have defined bookmarks. Each row of data equals a new Word file. Can someone assist with this? Attached is a sample Excel sheet.
GetCellValue will not work, as there will be 100+ rows of data.
Are you trying to concatenate the row data into a single bookmark, or will there be 11 bookmarks? I am not sure why GetCellValue wouldn't work if it is the latter since you are working on a single row at a time. In either case, I don't think GetCellValue is going to be an impediment to your speed. You could use ExportData to get a DataTable of the entire worksheet, but ultimately, you're still iterating each row and then editing/saving a new Word doc for each one.
I think the most obvious solution here is;
Create an automation to open and iterate through your spreadsheet and extract what you need from it.
As you complete a row, add a value into a new column indicating you have processed that column. This can be used when retrieving a row to allow you to stop in the middle if required (or the power goes out, etc...).
Create an automation to open (if not already opened), update the bookmarks, then save your Word doc.
Create an automation to copy the Word doc file to a new location (this would likely be faster than a Save As in the Word doc and allow you to always operate against the same file in the same location.
Let me know if that makes sense.
Posted: 7 months ago
Updated: 7 months ago
Posted: 9 Jun 2020 16:49 EDT Updated: 10 Jun 2020 8:35 EDT
Thank you for your quick response. I figured it out...
I ended up using the Excel Connector and added a range for the columns. Then used the forLoop to iterate through the Excel rows and used the GetCellValue to extract the data. From there I updated the Word bookmarks and saved the file as PDF. Attached is the screen shot of my result.