Using Excel to generate Word macros

[zSplash]

Please help me think this out -- maybe my plan is faulty.

WorksheetX is updated daily. From the values in WorksheetX, I want to
generate macros in Word (i.e., I want Word Macro cc7 to get data from row 7
of WorksheetX, cc8 to get data from row 8 of WorksheetX,etc.). Is this a
good way to accomplish what I want (or, would it be better to use Word
exclusively, and avoid using Excel? Personally, I find Excel easier to work
with...)

TIA

 

[Ed]

It sounds like you're trying to create a report using Word containing data
from your workbook. I've based my comments on that assumption. If I'm
incorrect, some of this might still apply.

From my view, your approach depends on: is your report a template, with only
certain, specific, unchanging data fields being updated? Or do which rows
of data are included change each time you generate a new report? If it's a
Word template, you can code in fields which draw data from your workbook,
esp. if you save the workbook with a convention such as "TodaysFile
mm-dd-yy" or create a copy of the update in a specific place with the same
name to replace the previous update.

But if your data locations change often, or you need to specify different
rows, then it might be better to work from the Excel side. Create a report
template in Word. In your Excel macro, call the Word doc and paste in your
data in the proper location, then SaveAs the Word doc.

HTH
Ed

 

[zSplash]

Hi, Ed. Thanks for the input.

Generally, what you assume is correct.

From my view, your approach depends on: is your report a template, with only
certain, specific, unchanging data fields being updated? Or do which rows
of data are included change each time you generate a new report?

Each macro contains data from the spreadsheet uniquely formatted, the
spreadsheet's data changes daily.

If it's a
Word template, you can code in fields which draw data from your workbook,
esp. if you save the workbook with a convention such as "TodaysFile
mm-dd-yy" or create a copy of the update in a specific place with the same
name to replace the previous update.

Based on the updating of the spreadsheet, allows quick entry into the Word
document/template (of the data in the spreadsheet).

But if your data locations change often, or you need to specify different
rows, then it might be better to work from the Excel side.

I have it set up so that the Spreadsheet is updated daily. The plan is to
take that updated data and make it "be" in (or, accessed by) the various
macros, which are named based on the row of the spreadsheet (cc7=row7,
cc88=row88 of spreadsheet).

Create a report
template in Word. In your Excel macro, call the Word doc and paste in your
data in the proper location, then SaveAs the Word doc.

What I was hoping to do is, in the Word document/template, call the various
macros, which "pull" from the Excel spreadsheet. I guess I have to have 300
unchanging separate macros (for 300 rows of the spreadsheet), which always
call the data from the changing spreadsheet.

Thanks again, for your input, Ed.

st.

 

[Tushar Mehta]

Have you looked at Word's Mail Merge capability? It lets you create a
template in Word, specify an XL data source, and indicate what tokens
in the Word document are replaced by the actual values from the XL
document.

If each of your rows of XL data correspond to a new Word document, all
the better. That is exactly what Mail Merge was designed to handle.

If for some reason you stick with your macro approach... it is hard to
imagine why you need a separate macro to access a different row of
data. Post a sample of these 300 macros and someone might be able to
suggest some streamlining.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions