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]

Also please note that all objects are fully declared!

Dim Goods As Word.Table
Dim oCell As Word.Cell

Dim appXL As Excel.Application
Dim rngWithData As Excel.Range

While is - sometimes - possible to get away with not fully qualifying objects, I would strongly recommend you do not do that. ALWAYS fully qualify things. At least until you are greatly confident in knowing exactly when you can get away with not doing so.

ASIDE: you can get away with this if you are working within a single application, but not if you are working between applications.

Gerry

 

[fumei]

And...........just to help you along with dealing with your 100+ files....

You can replace a bookmark - and it is dynamic and repeatable - with:

Sub FillBMWithTable(strBM As String, _
   TableRowCount As Long, _
   TableColCount As Long)
Dim yadda As Table
Set yadda = ActiveDocument.Tables.Add(ActiveDocument.Bookmarks(strBM).Range, _
      TableRowCount, TableColCount)
With ActiveDocument
   .Bookmarks(strBM).Delete
   .Bookmarks.Add strBM, yadda.Range
End With
End Sub

So for each of your files, you can pass in the bookmark name, and - dynamically - the Excel range row/column counts.

Call FillBMWithTable("Section_2", _
      rngWithData.Rows.Count, rngWithData.Columns.Count)

"Section_2" can of course be a variable as well.

The only issue for me (as I am a total idiot when it comes to Excel) is this:

"Plus the first time section_2.xls might have just one row of data, the next time it could have 25 rows."

THAT means your instruction:

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

is, in fact, variable.

It may be "A1:C6", but it could be "A1:C23"

THAT means you need to determine the range of cells with relevant/actual values. I have not a clue as to how to do that.

Gerry

 

[fumei]

As an aside, this has made me think about getting a table into a bookmark.

The code above builds a table (with given row/column counts) at a bookmark. Although technically, it builds it immediately after it. In other words, the table is not IN the bookmark.

If anyone is interested, I put together procedures that can take any table and copy it into (inside of) a bookmark. This can also take a string parameter that can be assigned to the table, thus enabling you to later call that table by name.

Thus, for example, you can put the cursor (Selection) into any table, execute the procedure and copy that table to any bookmark. Not after the bookmark - as a copy/paste operation would do - but actually inside the bookmark range itself.

And of course the bookmark could be in the same document, or in a different document.

Gerry

 

[WBURKERT]

Gerry,

What do you think about changing:

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

with

Set rngWithData = mySpreadsheet.Application.Range("Print_Area").Value

to decide on the range that will be different for worksheets?

 

[SkipVought]

Dim aryWithData, i as integer, j as integer
aryWithData = mySpreadsheet.Application.Range("Print_Area").Value

for i = 1 to ubound(aryWithData, 1)
  for j = 1 to ubound(aryWithData, 2)
    debug.print i,j,aryWithData(i,j)
  next
next

Skip,

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

 

[WBURKERT]

Hello Skip,

Your array code looks interesting to me but I am not sure where it would go, especially with all the additional code that I am trying to understand from fumei. Right now I have his 14:26 example working with my .doc and .xls. No further with his additional code. I was pleased to see it work this afternoon and left for the day to begin working from home. The plan is have something to demonstrated tomorrow.

With your code, do I just add it in and then change all the rngWithData with aryWithData symbols?

I think I need to change my WORD doc that contains all the bookmarks needs to change to a template so when I run this macro I won't have to worry about deleting WORD tables before adding 'fresh' ones, nobody cares about a previous table and all tables/bookmarks will be written every time.

Then I need to handle the formatting of the WORD table which should be fun for a novice who is determined like me, and the great support from guys like you and Gerry.

Then I plan on wrapping the whole thing up into a FOR NEXT LOOP incrementing the SECx.xls and Section X bookmarks get it done.

I night of coffee ahead. Hope to hear from you with some brilliant coding.

Will

 

[WBURKERT]

Everything is working quite nicely as I am now working on the table format and learning all about tables in Word. Interesting that my boss showed me and liked 'greenbar' style and I first thought how the heck I could do that manually not knowing the table styles existed. Too bad he didn't know the history of a greenbar report, the young pup that he is . Getting the WORD doc populated with a couple hundred tables throughout is so nice and cool. You guys helped me write a nice Excel macro to merge two excel spreadsheets together then chop it up into seperate workbooks for publishing into the WORD doc. The WORD doc has the cover page, Table of Contents, Index, hundreds of sections with text and green bar tables. All the formulas handled by Excel and all the publishing handled by Word - the Best of two worlds!

I will post this working macro for your comments, might be useful for someone else once the finished objective is revealed

 

[SkipVought]

The reason was that you had posted assigning a range of VALUES to a RANGE. That does not compute.

Hence, the code assigning a range of VALUES to an variant ARRAY.

Skip,

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

 

[fumei]

I think I need to change my WORD doc that contains all the bookmarks needs to change to a template so when I run this macro I won't have to worry about deleting WORD tables before adding 'fresh' ones, nobody cares about a previous table and all tables/bookmarks will be written every time.

Not a bad idea. Although if you replace the bookmark contents, then this is not an issue.

2. Not sure why, or how, you are doing a For Next loop, but it sounds more like you could use the Dir() function to get your files.

Gerry