Setting up and filling a Word document table from Excel

[DJCNOR]

I'm writing a VBA macro from Excel. The user supplies the information for an invoice by responding to Userforms in the Excel environment. The macro takes the input information, adds tied information (addresses tied to recipient names, product descriptions and prices tied to product numbers, etc.) and puts them into an Excel sourcesheet to be incorporated automatically into the word document, which already exists as a template. I have solved the problem of getting the appropriate information in at bookmarks in the document and getting to the bookmark in the document that indicates where the table with the product information will be.

I need to create the table in the word documents with a number of rows appropriate to the different types of products (they've ordered 1 of A, 3 of B, which would be two rows) (no problem with the counting bit) and fill it with the data from the Excel sourcesheet. Entries in the 3rd column of the table will vary from one line to ten, with some lines involving the incorporation of data from the Excel sourcesheet (serial numbers, commodity codes). For now, can you just help me with getting the table created in the word document?

With the word application open and the appropriate word document open, the bookmarked data already in, and the cursor at the place where I want the table (all done by the macro), this does not work, but probably show how much a beginner I am at this.

Dim Goods as Table
Goods.Add(tblrng,iRows,5)

DJCNOR

 

[SkipVought]

"I need to create the table in the word documents..."

and the reason is...?

What would prevent you form doing the entire thing in Excel?

Skip,

_[red][/red]

 

[CautionMP]

DJCNOR,
In a new blank document did you try recording a macro to create the table? It should give you a framework that you could then incorporate into your Excel macro.

Just a thought,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[DJCNOR]

Skip,

I meant that I need to creat the table in the Word document from the Excel macro.

Caution MP,

I didn't do that because I didn't know how to record a macro for the creation of a tabe that would vary as to the number of rows. I don't want to ask the user how many rows, since that information is already available from the Excel sourcesheet by counting the number of different products.

Thanks for helping me make the problem clearer.

Donna

 

[SkipVought]

Donna,

My question still stands. Why do you NEED to create the table in Word?

What would prevent you from doing EVERYTHING in Excel and not use Word at all for this application?

Skip,

_[red][/red]

 

[CautionMP]

DJCNOR,

"Yeah, why dont't you rely on the rich WYSIWYG text capabilities of Excel."

The macro in Word is not your end product, it is a template you can use to create your Excel macro.

For example, the first recorded macro creates a new table with five columns and two rows. You could use the same routine in Excel (with a couple of small changes) and let your workbook dictate what arguments you supply for the row and column arguments. The second would simply allow you to add a new row to the table on the fly.

Sub Create5x2Table()
  ActiveDocument.Tables.Add Range:=Selection.Range, [b]NumRows:=2[/b], [b]NumColumns:= _
    5[/b], DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    wdAutoFitFixed
End Sub

Sub InsertRow()
Selection.InsertRowsBelow 1
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[DJCNOR]

Skip,

The end result that my boss wants is a Word document. The reson for getting Excel involved is that pieces of the document need to match dot for dot in order for the shipment not to end up held by Homeland Security with huge storage charges put on it while you track down the stray comma.

MC Caution,

I understand that you adapt recorded macro codes. I just tried yours inserted into the Excel macro.

Create5X2Table()

was rejected as a syntax error.

Donna

 

[SkipVought]

"...my boss wants is a Word document..."

Well, is there a REASON that it MUST be a Word document?

Just because the boss THINKS this is the best place, is not necessarily so.

The end result is to produce a valid and complete document without having to do handstands and contortions.

It would be akin to the manager telling the carpenter, "I want you to use a screwdriver instead of a hammer to drive nails!" Is it POSSIBLE to drive a nail with a screwdriver? Possible, but with great difficulty. Is that NOT the choice of the craftsman?

Skip,

_[red][/red]

 

[DJCNOR]

Hi Skip,

Maybe it's because it's the end of the day here in the UK that I'm not understanding this. Are you saying that I can create something in Excel that has all the Word Document type bits like the header and the logo and the the footer and the text that is going to be the same for each and every invoice/shipping document, all formatted in the correct way, in Excel. I guess you are.

Me, I'm a biochemist doing an office job, who was asked if I could take up this project that a supposed new graduate in programming couldn't manage to do last summer. Since I learned programming a long time ago, and since I'm generally pretty good at learning from books and examples (Ph.D. somewhere along the line), I'm trying. I've managed all but this danged table, but can't seem to find the code I need for this part.

Sorry to bother you, but I will not give up.

Donna

 

[SkipVought]

It's possible. I don't know ALL your requirements.

For all I know, Word may be the better choice. But often, it is a choice made on what someone likes or is comfortabel with or aware of, rather than being the BEST solution amoung the available tools.

Logos in Excel - YES

Header on multiple sheets - quallified yes, Word has better functionality

Footers on multiple sheets - quallified yes, Word has better functionality

How many sheets or pages are we talking about?

Skip,

_[red][/red]

 

[CautionMP]

DJCNOR,
Did you make the changes to adapt it to Excel before you tried to run it?

As typed it will cause errors in Excel because the [tt]ActiveDocument[/tt] and [tt]Selection.Range[/tt] objects don't exist in Excel.

Creating the table is not all that hard, why don't you post the code you have (from Excel), if we can see it we can provide a more direct answer that will work with your exisiting routine.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[DJCNOR]

Skip,

Varies, a lot. I'd say up to 10. And different text on each page, only with parts that have to match exactly.

Donna

 

[SkipVought]

With those kind of requirements (and Homeland Security) it will probably be safer to try to make Word work.

I'm sure that CMP can help you develop a working solution.

Skip,

_[red][/red]

 

[DJCNOR2]

Hello,

I'm home now, which is why I'm now DJCNOR2. I wanted to reply to Caution MP, but had to leave to catch my ride.

Since I'm home I don't at the moment have access to my code. There wasn't much related to the table other than what I posted at the beginning (though that came after the things I mentioned. I had already tried several ways of coding the table creation and then deleted them when they didn't work. The previous code used for preparing to insert the bookmarked text, though, included using ActiveDocument and Selction under With Wrd (wrd being set as the Word Application).

I'll be trying again tomorrow. Thanks.

Donna

 

[CautionMP]

DJCNOR (DJCNOR2),
Here are two samples.

The first ([tt]Add_Row_to_Table()[/tt]) will let you add rows to a table that already exists in your Word template.
The second ([tt]Range_to_Word_Table_at_Bookmark()[/tt]) will create a brand new table at a specific boodmark in your Word template.

Each routine uses different methods to refer a 'cell' in both Excel and Word. This was done intentionally to demonstrate various ways so you can decide which method works best for you.

[navy]Sub[/navy] Add_Row_to_Table()
[navy]Dim[/navy] appWord [navy]As Object[/navy]
[navy]Dim[/navy] docDest [navy]As Object[/navy]
[navy]Dim[/navy] objCell [navy]As Object[/navy]
[navy]Dim[/navy] lngRow [navy]As[/navy] Long, lngColumn [navy]As Long[/navy]
[navy]Set[/navy] appWord = CreateObject("Word.Application")
[green]'so we can see what's happening during testing[/green]
appWord.Visible = [navy]True[/navy]
[green]'open an existing document[/green]
[green]'it contains one table with one row (column headings)[/green]
[navy]Set[/navy] docDest = appWord.Documents.Open("C:\Table.doc")
[green]'select the table[/green]
docDest.Tables(1).[navy]Select[/navy]

[green]'Fill the row with data from active workbook[/green]
[green]'assumes Excel data starts in A2, there are 4 rows[/green]
[green]'of data with 5 columns[/green]
[navy]For[/navy] lngRow = 2 [navy]To[/navy] 5
  [green]'Insert a new row[/green]
  appWord.Selection.InsertRowsBelow 1
  [green]'Loop through all the cells in the current table row[/green]
  [navy]For Each[/navy] objCell [navy]In[/navy] appWord.Selection.Cells
    [green]'count the columns[/green]
    lngColumn = lngColumn + 1
    [green]'fill in the current table.cell with data from the workbook[/green]
    objCell.Range.Text = ActiveSheet.Cells(lngRow, lngColumn)
  [navy]Next[/navy] objCell
  [green]'reset the column counter[/green]
  lngColumn = 0
[navy]Next[/navy] lngRow
[green]'so we can toggle over to Word and see what happend[/green]
[navy]Stop[/navy]
docDest.Close [navy]False[/navy]
[navy]Set[/navy] docDest = [navy]Nothing[/navy]
appWord.Quit
[navy]Set[/navy] appWord = [navy]Nothing[/navy]
[navy]End Sub[/navy]

[navy]Sub[/navy] Range_to_Word_Table_at_Bookmark()
[navy]Dim[/navy] appWord [navy]As Object[/navy]
[navy]Dim[/navy] docDest [navy]As Object[/navy]
[navy]Dim[/navy] objCell [navy]As Object[/navy]
[navy]Dim[/navy] Goods [navy]As Object[/navy]
[navy]Dim[/navy] rngWithData [navy]As[/navy] Range, rngRow [navy]As[/navy] Range, rngColumn [navy]As[/navy] Range
[navy]Set[/navy] rngWithData = ActiveSheet.Range("A2:E5")
[navy]Set[/navy] appWord = CreateObject("Word.Application")
[green]'so we can see what's happening during testing[/green]
appWord.Visible = [navy]True[/navy]
[green]'open an existing document[/green]
[navy]Set[/navy] docDest = appWord.Documents.Open("C:\Table.doc")
[green]'add a new table at the bookmark TableHere[/green]
[green]'use the row and column count from the Excel to size new table[/green]
[navy]Set[/navy] Goods = docDest.Tables.Add(docDest.Bookmarks.Item("TableHere").Range, _
                           rngWithData.Rows.Count, rngWithData.Columns.Count)
[green]'Cycle through each row in the Excel range[/green]
[navy]For Each[/navy] rngRow [navy]In[/navy] rngWithData.Rows
  [green]'Cycle through the columns in the current Excel row[/green]
  [navy]For Each[/navy] rngColumn [navy]In[/navy] rngRow.Columns
    [green]'Figure out which Excel cell we are in and find the[/green]
    [green]'corresponding cell in the Word table[/green]
    Goods.Cell(rngColumn.Row - rngWithData.Row + 1, _
          rngColumn.Column).Range.Text = rngColumn.Value
  [navy]Next[/navy] rngColumn
[navy]Next[/navy] rngRow

[green]'so we can toggle over to Word and see what happend[/green]
[navy]Stop[/navy]
[navy]Set[/navy] rngColumn = [navy]Nothing[/navy]
[navy]Set[/navy] rngRow = [navy]Nothing[/navy]
[navy]Set[/navy] rngWithData = [navy]Nothing[/navy]
[navy]Set[/navy] Goods = [navy]Nothing[/navy]
docDest.Close [navy]False[/navy]
[navy]Set[/navy] docDest = [navy]Nothing[/navy]
appWord.Quit
[navy]Set[/navy] appWord = [navy]Nothing[/navy]
[navy]End Sub[/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[DJCNOR]

Thanks, CMP,

I will study these and report back how I do.

Donna

 

[DJCNOR]

Thanks, Caution,

Your examples really did help. Here’s what I did to get my Excel workbook putting info into the bookmarks of a Word document.

First, a bit of Diming of Excel data as strings, s2 being one of these

Set Wrd = CreateObject("Word.Application")
Const wdGoToBookmark As Long = -1
Const wdFieldEmpty As Long = -1
With Wrd
.Visible = True
.Documents.Open ("X:\Donna\Dummy Invoice.doc") 'Change Here
Set BMRnage = .ActiveDocument.Bookmarks("Addr1").Range
.Selection.Goto What:=wdGoToBookmark, Name:="Addr1"
.Selection.TypeText Text:=s2

There were a number of those last three lines, each with a different string. Yes, I know that can be a loop, and it will be in the module’s next incarnation.

Then, to set up the table, I Dimmed some ranges and an array, so that putting most of the entries into the table could be a loop. And the following set up the table and put in the simple entries.

Set ProductTblRng = .ActiveDocument.Bookmarks("ProductTbl").Range
.Selection.Goto What:=wdGoToBookmark, Name:="ProductTbl"
Set Goods = .ActiveDocument.Tables.Add(ProductTblRng, iRows, 5)
iNextCell = 1
For iNextCell = 1 To iRows
Goods.Cell(iNextCell, 1).Range.Text = sNP(iNextCell)
Goods.Cell(iNextCell, 2).Range.Text = sPN(iNextCell)
Goods.Cell(iNextCell, 2).Range.Text = sPP(iNextCell)
Goods.Cell(iNextCell, 2).Range.Text = sPT(iNextCell)
Next iNextCell

End With

This solves all my current problems except for the one complicated entry on each line of the table, which is the description of the product. This can be up to 10 separate lines of text, several of which contain variables like serial numbers and commodity codes. I’m thinking the simplest way might be to build the whole darn entry in Excel as one long string and then just add it to the loop, but I don’t know yet how to signal New Line in such a structure. I’ll try to figure that out for myself.

Donna