MACRO to put section_2.xls to WORD Bookmark Section_2 2

[WBURKERT]

Please, please help - my boss is unreasonable and I do not want to do things manually, especially more than once.

I have about 100 EXCEL workbooks that vary in size that I would like to put into a single word document at a pre-defined bookmarks, replacing any table that might be there already. (The first time the bookmarks will be there but no data/table; subsequent runs may or may not have data/table present)

I would like to put the data into WORD as a Table.

Example:
section_2.xls should goto Listings.doc at bookmark [section_2] as a WORD Table, if possible
section_3.xls should goto Listings.doc at bookmark [section_3] as a WORD Table, if possible
and so on.

I have tried EMBEDDING and LINKING .xls but it has crazy limitations because of the OBJECT thing. I think that the WORD tables might cross page break boundries and OBJECTs can not. Plus the first time section_2.xls might have just one row of data, the next time it could have 25 rows.

I nerveously await some assistance while I run some interference with my boss. Thanks in advance.

Will Burkert

If someone could assist me with getting one .xls to a bookmark I think I can figure the rest out.

Thank-you, Will Burkert

 

[fumei]

I do not know how to get an xls into a Word table. You embed them, or link them. If I understand correctly you do not want to do this.

Then you will have to literally get the dat from each cell of the xls, and build a Word table cell by cell.

Gerry

 

[SkipVought]

Also, I would assume that each of your workbooks, has one and only one worksheet, on which is one and only one table; a table being defined as a range of contiguous cells containing data.

Yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Yes, you may have to careful of the use of the word "table".

Gerry

 

[WBURKERT]

Hi Skip,

Yes, each of the section_x.xls woekbook contains just a single worksheet. The entire contents of the worksheet will be in the WORD table.

Not sure why fumei answered your question as I do not know that person nor have I responded outside this post??

I have been searching the forum for solutions and I think the solution will involve WORD Bookmarks and I have added them to my WORD .doc. I have been thinking about how to delete existing tables and think the solution will involve a WORD .dot template. I figure if I am going to have the macro place all WORD tables everytime then who cares what table data was there from last time. My understanding of WORD templates is not great but I presume it is just a 'template' that gets filled in and ALWAYS saved as a WORD .doc.

 

[WBURKERT]

This is what I have come up with so far -
I get a compile error when it runs so I have no idea how well it works.
Please shed a little light on my obvious problem(s)

I have referenced the EXCEL object library so I can use the Excel in the Project. That seems to be correct

Sub Section_X_to_Word_Table_at_Bookmark_X()
Dim appWord As Object
Dim docDest As Object
Dim objCell As Object
Dim Goods As Object
Dim rngWithData As Range
Dim rngRow As Range
Dim rngColumn As Range

Dim mySpreadsheet As Excel.Workbook
GetObject ("F:\Documents\ES Pricelist SUT\Sec2.xls")

Set rngWithData = mySpreadsheet.Range("A1:C6")

Set appWord = CreateObject("Word.Application")

appWord.Visible = True

Set docDest = appWord.Documents.Open("C:\Table.doc")

Set Goods = docDest.Tables.Add(docDest.Bookmarks.Item("Section_2").Range, rngWithData.Rows.Count, rngWithData.Columns.Count)

For Each rngRow In rngWithData.Rows

For Each rngColumn In rngRow.Columns

Goods.Cell(rngColumn.Row - rngWithData.Rows + 1, rngColumn.Column).Range.Text = rngColumn.Value ' <- I get Compile error: Method or data member not found

Next rngColumn

Next rngRow

Stop
Set rngColumn = Nothing
Set rngRow = Nothing
Set rngWithData = Nothing
Set Goods = Nothing
docDest.Close False
appWord.Quit
Set appWord = Nothing


End Sub

 

[PHV]

I get a compile error
Which error on which highlighted line ?

Anyway, I'd replace this:
GetObject ("F:\Documents\ES Pricelist SUT\Sec2.xls")
with this:
Set mySpreadsheet = GetObject("F:\Documents\ES Pricelist SUT\Sec2.xls")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

PHV - Thanks for the follow-up. The line starting with 'Good.Cell( . . .' is the line that causes the error. I added a comment to help, the .Value word is highlighted in Yellow.

I am going to implement your suggestion and report back to this post as to how it goes. Thanks, I'll be right back.

 

[WBURKERT]

Made a small change to 'Set mySpreadsheet . . .' still getting a compile error at 'Goods.Cell . . . ) - see comments. Still don't if this macro works; would love to see a solution from Skip.

Sub Section_X_to_Word_Table_at_Bookmark_X()
Dim appWord As Object
Dim docDest As Object
Dim objCell As Object
Dim Goods As Object
Dim rngWithData As Range
Dim rngRow As Range
Dim rngColumn As Range

Dim mySpreadsheet As Excel.Workbook
Set mySpreadsheet = GetObject("F:\Documents\ES Pricelist SUT\Sec2.xls") 'changed to PHV's suggestions - thanks PHV

Set rngWithData = mySpreadsheet.Range("A1:C6")

Set appWord = CreateObject("Word.Application")

appWord.Visible = True

Set docDest = appWord.Documents.Open("C:\Table.doc")

Set Goods = docDest.Tables.Add(docDest.Bookmarks.Item("Section_2").Range, rngWithData.Rows.Count, rngWithData.Columns.Count)

For Each rngRow In rngWithData.Rows

For Each rngColumn In rngRow.Columns

Goods.Cell(rngColumn.Row - rngWithData.Rows + 1, rngColumn.Column).Range.Text = rngColumn.Value ' <- Getting compile error here with .Value being highlighted. Same errror as before - compile error: Method or data member not found.

Next rngColumn

Next rngRow

Stop
Set rngColumn = Nothing
Set rngRow = Nothing
Set rngWithData = Nothing
Set Goods = Nothing
docDest.Close False
appWord.Quit
Set appWord = Nothing


End Sub

 

[PHV]

Why this for a row number ?
rngColumn.Row - rngWithData.Rows + 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

Hey PHV - Why? Because I copied this macro from another post in this forum. I think I am pretty good with fixing macro's to my requirements after I know they work as designed. I am not sure on any of my macro, especially because I can't see it run.

Maybe you can help me get going in the right direction. I was praying for a macro yesterday because of the pressure I am under to perform this all manually. Luckily my boss is out this morning so I am getting a little more time to get something working before he pulls the plug. Argh.

 

[WBURKERT]

If I comment out my Goods.Cell line and single step through the macro I get an error early on which may be the cause downstream??
I get an error 438: Object doesn't support this property or method on the line 'Set rngWithData = mySpreadsheet.Range("A1:C6")'
I suspect this causes havoc with the line 'Goods.Cell . . ' and might be the root cause of this bug. I say this bug because I'm sure there is goiing to be more than one since I am new to VBA.

 

[PHV]

I'd try this:
Set rngWithData = mySpreadsheet.ActiveSheet.Range("A1:C6")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

Hey PHV - Thank-you for the suggestion but it errors out and I thiink it is because of the line of code directly above Set rngWithData.

When you make a suggestion, are they working for you? In my code?

 

[fumei]

Can we clear something up here? What application is running this? Word? If it is Word, why are you making a Word instance?

Set appWord = CreateObject("Word.Application")appWord.Visible = True

And if it running from Excel (which I suspect it is), why are you stating:
"I have referenced the EXCEL object library so I can use the Excel in the Project. "

Gerry

 

[WBURKERT]

Fumei,

Word in running the macro (because I found it that way on the forum)
Why is it making a Word instance (because I found it that way on the forum)
By showing everyone that I am trying really hard I searched the forum and found examples of code that was close to doing what I hoped for. I was hoping someone would help tweak my example to work.
Everything seems to be going backwards at this point - i still have no working code and no change/suggestion seems to be working either. My boss is in and I expect a status query any moment.
Am I trying to do something impossible? It seems that bookmarks in Word are correct, referencing the Excel Object Library seems correct. Building the table in Word seems to be correct. The big problem I am having is getting the range from a worksheet, which is likely caused by the way I am trying. I don't think ActiveSheet is correct because no Excel spreadsheets is opn when runnning the macro and because there are 100+ spreadsheets there is no plan to open them before running the macro.
Do you think I am close to a solution?

 

[PHV]

because no Excel spreadsheets is opn
How do you understand this ?
Set mySpreadsheet = GetObject("F:\Documents\ES Pricelist SUT\Sec2.xls")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

PH - I put that line in per your advice. It seems to me that GetObject is the wrong idea. All I am trying to do is access a spreadsheet named Sec2.xls so I can figure it's range so i can start building a table in Word.

Instead of asking me a lot of questions which I probably don't know the answer anyway could someone show me how to get a workbook with just one worksheet into a Word table. My original code seems like it is close, can't you guys tweak it a little to make it work? I sure would appreciate it and will be sure to show my appreciation through a monetary reward. Thanks in advance, Will.

 

[fumei]

So many things....

The FIRST one, is this:

Set rngWithData = mySpreadsheet.Range("A1:C6")

This is invalid. You need a Worksheet object. mySpreadSheet is a WorkBook object.

The code you are working with is too complicated. It does not have to be that way. The following works, but I hope you are aware that this creates a Word table with plain-text values from the Excel data. There is NO - repeat NO - linkage back to the original Excel data.

Option Explicit

Sub Section_X_to_Word_Table_at_Bookmark_X()
' Word variables
Dim docDest As Word.Document
Dim Goods As Word.Table
Dim oCell As Word.Cell

' Excel variables
Dim appXL As Excel.Application
Dim rngWithData As Excel.Range
Dim mySpreadsheet As Excel.Workbook

' row/column counters
Dim RowCount As Long
Dim ColCount As Long

Set appXL = CreateObject("Excel.Application")
Set mySpreadsheet = GetObject("C:\Whatever1.xls")
Set rngWithData = mySpreadsheet.Worksheets(1).Range("A1:C6")

RowCount = rngWithData.Rows.Count
ColCount = rngWithData.Columns.Count

Set docDest = Documents.Open("C:\Table.doc")
Set Goods = docDest.Tables.Add(docDest.Bookmarks("Section_2").Range, _
      RowCount, ColCount)


For Each oCell In Goods.Range.Cells
   oCell.Range.Text = rngWithData.Cells(oCell.RowIndex, _
      oCell.ColumnIndex).Value
Next

Set rngWithData = Nothing
Set Goods = Nothing

End Sub

Comments:


1. note that there are far fewer objects/variables. You do not need them

2. note the creation of the Excel instance (assuming you DO have the Excel reference)

3. note how the data is inserted into the Word table.

For Each oCell In Goods.Range.Cells
   oCell.Range.Text = rngWithData.Cells(oCell.RowIndex, _
      oCell.ColumnIndex).Value
Next

This takes the cell object in the Word table, gets ITS rowindex/columnindex, and uses that to get the Excel range cell location.

Word cell text = Excel.Range.Cell(Word cell rowNUM, Word cell colNUM).Value

As the WORD cell indices match EXACTLY to the Excel range cell indices (the table was created that way)...it works.

Word cell (2,3) - Excel.Range.Cell(2,3)

ERGO

Word Cell (2,3) TEXT = Excel.Range.Cell(2,3).VALUE

Gerry

 

[fumei]

Also, this code is being run from Word, from some OTHER document than the tables.doc.

Gerry