Excel to Word

[Guest]

I have just completed a userform with tons of fields. Once submitted it
places the data into the worksheet. The worksheet itself has 141 columns of
different values. Now I need to have some sort of code that fits into a
command button from the toolbox on the actual worksheet to transfer data form
each row that has data into a word document. Each row has to be it's own
paragraph. Is this possible? A short sample of the data layout:

Column Headers:
ID Description Total Location Date Coats
Labor (hrs)
20 Room Painted $500.00 Living Room 9/17/05 3 5
30 Room 2 Paint $600.00 Bath 9/20/05 2 2

I would love it to be in word in the following format:

ID: 20, Description: Room Painted, Total: $500.00, Location: Living Room,
Date: 9/17/05, Coats: 3, Labor (hrs): 5

ID: 30, Description: Room 2 Paint, Total: $600.00, Location: Bath, Date:
9/20/05, Coats: 2, Labor (hrs): 2

Basically it would continue until it reached a row with no data. Can this be
done? Thanks in advance!!

 

[Don Guillett]

best not to multipost

 

[Guest]

This is more of a Word thing. Create a document and use the Mail Merger
option to get the data. Using the Next Record function you can get multiple
records on the same page. I can't remember but you may have to use the
Catelogue method of merging...

HTH

 

[Guest]

I figured it would be more of a VBA thing in Excel since I am transmitting
the data from Excel to Word.

 

[Ed]

It could go either way; I've done both. Here's how I would try it.
(Warning: it may not be pretty and it probably isn't the easiest and best,
but it's how I would approach it.)
Set a range to encompass all the rows with data
Set objects to Word and a new document
With DataRange
For i = 1 to DataRange.Rows.Count
String1 = "ID: " & .Cells(i,1).Value & ", "
WordDoc.Selection.TypeText String1 & vbCr
String2 = "Description: " & .Cells(i, 2).Value & ", "
WordDoc.Selection.TypeText String2 & vbCr
etc. to last cell
Next i
Save and close Word doc; quit Word, release objects
Save and close Excel file

HTH
Ed

 

[Guest]

There's no way I can get this into an a specific area in an existing
template, huh?

 

[Guest]

Okay, this should be my final question: is there a way to skip over the cells
if they are blank?

 

[Ed]

You've got to be sure what "blank" is - if it has any kind of formula, it
may not be recognized as blank. If it has any spaces, it will not be
recognized as blank. If it's truly empty, then just If the value:
If .Cells(i, XX).Value <> "" Then
' Do something
End If

If you want to skip the whole row if one cell is blank, you've got to
iterate through all the cells first to check before sending anything to a
string.

Ed
(PS - gone for the day; be back tomorrow)