Importing Excel data into Word

[NBA]

i'd like some suggestions on how to proceed. i have an automation task i'd
like to accomplish.

instead of cutting and pasting data from excel to word, Iid like to
dynamically generate a set of report paragraphs from each row in an excel
spreadsheet.

i have a report format in a word table. the excel spreadsheet has the
report data and each column heading corresponds to a data field type within
the word table. each row in the spreadsheet would be a different set of data
and would need to be on a separate page. so, i'd like to programmatically ...

fill in the word table with the first row of data,
then insert a page break and generate a similarly formatted table with row 2
data,
and so on until all of the rows with information have been imported into the
word tables.

some of the key things to consider are that I will not have a static set of
excel rows for each report, so the code has to figure out how much data i
have and make the appropriate number of pages in word.

what is the best approach here? is it to use fields?

thanks for any feedback, suggestions, or pointers to examples.

NBA

 

[Helmut Weber]

Hi,

what is the best approach here?

Hard to say, there are a million ways.
I'd use bookmarks. But you have to
learn about inclusive and exclusive
bookmarks first. For controlling Excel
from Word see:
http://www.word.mvps.org/faqs/interdev/ControlXLFromWord.htm

For checking how many rows with data you've got,
there are many ways, too. E.g. depending on whether
empty rows and rows with data can appear anywhere
in a sheet or whether there is some kind of regularity,
like first all rows with data and then only empty rows.

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[NBA]

Helmut,

Thanks for the pointer, I'll have a look.

Since I don't know in advance how many rows I'll have until the data is
ready to be processed, will bookmarks need to be named up front?

The data will be ordered with data starting in row 2 and continuing on with
the following rows until there is no more data. There will be no blank rows
between sets of data. It is just the quantity of rows that will vary.

 

[Helmut Weber]

Hi NBA,

first the easy part, here from Excel:

MsgBox Cells(Rows.Count, 1).End(xlUp)
gives you the last not empty cell in row 1.

MsgBox Cells(1, 1).End(xlDown)
gives you the first not empty cell in row 1.

Now, where should the values of these cells go?
Kind of tricky!

Bookmarks are of little use,
as they are unique to a document.
Creating bookmarks on the fly is possible,
but where should they be created?

I'd try something like this:

Information from cells(2,1) will be inserted
after character x of section 1.
Or in paragraph x of section 1.

Information from cells(3,1) will be inserted
after character x of section 2.
Or in paragraph x of section 2.

Nice little project.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"