PDA

View Full Version : Pasting some tables form Excel in Word



JvL
09-20-2007, 10:42 PM
Dear all,

I wrote some code (VBA Excel) to paste automaticly some tables from Excel in my Active Word Document.

Steps wanted:
1: Select table (no problem)
2: Go to active Word Document (no problem)
3: Past table
4: Goto end of table in Word
5: Inser pagebreak
6: Goto Excel

Then the code can repeated for another table.

My code is:

Public Sub ExW()
Dim wdApp As Object
Set wdApp = GetObject(, "Word.Application")
Worksheets("Blad1").Activate
Range("A1:A4").Select
Selection.Copy
With wdApp.Visible = True
wdApp.Activate
Set WordDoc = wdApp.ActiveDocument
End With
With wdApp
.Selection.PasteSpecial DataType:=wdPasteBitmap
.Selection.InsertNewPage
End With
End Sub

Problems:
1) It gives a faillure on de line ".Selection.InsertNewPage" ???
2) How to go to the end of my pasted table?

Your help is welcome!

Charlize
09-20-2007, 11:58 PM
Try this one.
- Template is word_tables.doc
- Two textbookmarks. Normal text in word document (bookmark1, bookmark2)
- after bookmark1 you insert your pagebreak
- The idea is to replace all the bookmark text with the info that you want to copy

Option Explicit
Dim Info_doc As Object
'the application
Dim WD As Object
Sub Fill_In_Info_From_Excel()
'where is the template located
Dim which_document As String
which_document = ActiveWorkbook.Path & "\word_tables.doc"
'need an instance of word
Set WD = CreateObject("Word.Application")
WD.Visible = True
Set Info_doc = WD.Documents.Open(which_document)
'*** code to manipulate your document
'replace the text in the document with text in cells
Worksheets(1).Range("A1:A4").Copy
Call Change_Bookmark("bookmark1", Range("A1:A4"))
Worksheets(1).Range("B1:B4").Copy
Call Change_Bookmark("bookmark2", Range("B1:B4"))
WD.Activate
Set Info_doc = Nothing
Set WD = Nothing
End Sub
Sub Change_Bookmark(Template_Value As String, New_Value As Range)
Dim oword As Object
For Each oword In Info_doc.Words
If oword.Text = Template_Value Then
oword.Paste
End If
Next oword
Set oword = Nothing
End Sub

JvL
09-21-2007, 12:53 AM
I highly appreciate your comments, the word document is not always the same. The idea is that we make our proposal in word. Doing this we will open excel, start VBA which put some tables in the word document that is already opened. I cannot refer to a document with a fixed name. The document will be in the same directory as the excel document that contains the tabels but most of the time there are more word documents in that directory.

Thx.

JvL

Charlize
09-21-2007, 01:32 AM
Are the tables in a separate sheet. Table 1 in sheet table1, table 2 in sheet table2 ? Since you are in word, control excel from there and copy what you want.

JvL
09-21-2007, 07:15 AM
The excel file contains a lot of sheets, so I suppose the use of Excel VBA to choose between the sheets (whole visible part of the sheet should be pasted as table rest is hidden) and to determine the sequence. Not all sheets have to be pasted and de content of sheets depends on the parts that will be offered the number and name of the sheets is not standard.

Thank you for your help.

TonyJollans
09-21-2007, 09:38 AM
Quite a few issues with that code :) - I'll assume they're typos.

Exactly how the paste works depends on settings in Word - depending on the version of Word. And on that depends how to proceed.

However to insert a manual page break, use:
Selection.insertBreak wdPageBreak

JvL
09-22-2007, 12:52 AM
Tony,

You mention "quite a few issues with that code" what do you mean with that, where did you see a typo?

I got a error on the code you present.

Thx, Jan

TonyJollans
09-22-2007, 04:02 AM
Sorry, Jan, perhaps I exaggerate. This bit:

With wdApp.Visible = True
wdApp.Activate
Set WordDoc = wdApp.ActiveDocument
is odd. There should be a line break between . and Visible, but if you are working in Word it will already be visible anyway. There is also no need to Activate the Word application, and you never use WordDoc.

Now, what error do you get? The most likely one would be wdPageBreak not being recognised due to not having a reference to the Word library but as you are using wdPasteBitmap I presume that's not the case

JvL
09-22-2007, 04:23 AM
You are right an error on wdPageBraek. As I wrote above first I want to paste my table, than go to the end of the table and insert a break. I'm looking for the right code now.

Thx

TonyJollans
09-22-2007, 04:42 AM
Try using 7 instead of wdPageBreak

JvL
09-22-2007, 11:02 AM
Sorry what's "7" ???

Thx

TonyJollans
09-22-2007, 11:48 AM
A number :)

Probeer wel het nummer zeven in plaats van het woord wdpageBreak.

JvL
09-24-2007, 07:27 AM
Tony,

Thx for your comments I tried a "7" but it results in the same failure message as below:

Fout 438 tijdens uitvoering:
Deze eigenschap of methode wordt niet ondersteund door dit object

Can you give me an advice?

Thx

TonyJollans
09-24-2007, 10:42 AM
Sorry - it should be:

.Selection.InsertBreak wdPageBreak
(note the dot) and not:


Selection.InsertBreak wdPageBreak

It has to reference the Word Selection and not the Excel Selection.

Charlize
09-25-2007, 04:53 AM
A possible way with a sneak preview of the sheet that you want to insert. Only the sneak preview coding has been done. I'll leave the importing of the chosen sheet to you. Hope you find a usefull way for it. You must set a reference to the excel library (in vbe - references - excel ...). If you don't like this you can declare excel as an object and create an instance of excel.

Anyway, as attachment two files. They have to be in the same directory. Open the word file and execute the macro show_me. The excel file is opened when using word, so no need to open it up front.