insert excel in word

[johnjuan]

I have code that copies a range from each worksheet and pastes them into Word. Trouble is, I cannot figure out how to past them into specific locations in the word doc. Any suggestions? I have tried bookmarks to NO solution. The code I am using looks like this: (also would appreciate suggestions on how to work from a template, and how to put in a template and save as )

[johnjuan]

uploaded example file

[johnjuan]

Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
'Set wdDoc = wdApp.Documents.Add

Set wdDoc = wdApp.Documents.Open(Filename:="C:\Documents and Settings\jrose\Desktop\KCA-Testing.doc")


For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
' insert page break after all worksheets except the last one


If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
' .InsertBreak Type:=wdPageBreak
End With
End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

[lucas]

Hey John,
I am short on time but it sounds like you need some help from the Word forum with this.....I am moving it for you.

[johnjuan]

thanks steve

[fumei]

Not quite following. You mention specific locations, but your code seems to inserting the worksheets at the end of the document. You can use a Word Range for this, to be cleaner, like this:Sub CopyWorksheetsToWord2()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check
' the Microsoft Word X.X object library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim r As Word.Range
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
Set r = wdDoc.Range

For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
With r
.InsertParagraphAfter
.Collapse 0 ' collapse to the end
.Paste
.Collapse 0 ' collapse to the end
' why is this here????
Application.CutCopyMode = False
.InsertParagraphAfter
End With
' insert page break after all worksheets except the last one
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With r
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
' BTW this is the same as Collapse 0
' .InsertBreak Type:=wdPageBreak
End With
End If
Set r = wdDoc.Range
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

I used Documents.Add rather than your specific file.

Essentially, your code works OK. What exactly are you trying to do? You could use Word bookmarks, you just have to use them properly, and some people - and ahem we know who they are...just kidding - take a little getting used to the Word Object Model, which is admittedly sometimes wonky from an Excel perspective.

Please use the underscore character to make code fit in the code window here.

Here is an alternative, using bookmarks. It clones a new Word document from a Word template (c:\temp\fromexcel.dot). That template has three bookmarks ExcelHere1, ExcelHere2, ExcelHere3.

The Excel file has three worksheets - i.e. they match the fact there are three bookmarks in the Word template. VERY important.

Excel Sheet1 (I did not bother renaming the worksheets as that is not relevant) will be pasted at bookmark ExcelHere1, Sheet2 at ExcelHere2, and Sheet3 at ExcelHere3.

There are no page breaks needed as they are already in the Word template.Sub CopyWorksheetsToWord3()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check
' the Microsoft Word X.X object library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim ws As Worksheet
Dim j As Long
j = 1
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:="c:\Temp\FromExcel.dot")

For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

[johnjuan]

Thanks Fumei I will try your new code essentially my code just threw the excel cells in one right after another, suing insertparagraphafter. I wanted to place them in specific locations, following certain already existing text, but the bookmarks didn't seem to work. I'll try your code in the am, as its 12:53 here Thanks a million for helping! John

[johnjuan]

Shoot, Fumei, I get an error on the following line: wdDoc.Bookmarks("ExcelHere" & j).Range.Paste this is after I placed three bookmarks in the document - ExcelHere1, ExcelHere 2 and ExcelHere3 error message is " The requested member of the collection does not exist" so I double checked my template, (fromexcel.dot), to make sure the bookmarks are there. Then I got: error message is "The method or property is not available because the clipboard is empty" Strange, because when I check the clipboard the excel range is in fact there, and in the spreadsheet its been selected. any advice? thanks for all your help.

[fumei]

Yikes, that is a couple of things.

1. the "not exist" could be because when the Paste is made, the bookmark is deleted. (There are ways around this.) However, that shouldnot be an issue, as you should be getting a brand new document from the template each time. If the bookarks are indeed in the .dot file, then they should be there in any document cloned from the template.

2. I am not sure what you mean by "then": "Then I got: error message is "The method or property is not available because the clipboard is empty""

Then? Do you mean something happened between the not exist error and this one? Not following.

On the other hand that is an odd error. Sure, if the clipboard IS empty, then it makes sense...but why is it empty?

Describe, step-by-step what is going on. Are you in fact doing standard debugging by Step executing?

[johnjuan]

The first error seems related to whether WinWord is running in the background. Closing it in task manager seems to clear it up. When I checked task manager there were 4 instances of WinWord open. Closing them all leaves me with just the clipboard empty error

So, for now I can forget about that first error message and I can think just about the empty clipboard error.

I did check the template, and it seems fine, has all the bookmarks in it.

I get the clipboard error, at the bottom of excel, it just reads copying data from excel.

It also ignores the first worksheet and starts copying only from the second. Don't know why it skips the first worksheet.

Yes, doing this step by step.

Many many thanks for your help Fumei.!!

I will continue to try and figure this out although any suggestion would be greatly appreciated. Thanks again, john

[fumei]

Yup...I was wondering when you would stumble on the instances.

You have:

Set wdDoc = Nothing

But you do NOT Close the document first. I would strongly suggest you do. Word is better than it used to be regarding memory management, but care has to be taken to clean up yourself.

As for skipping the first worksheet...ummmm...I don't know. It does not do that for me.

[johnjuan]

Fumei, not sure if you are still checking this thread or if I should start another - please advise

I got the code you suggested running fine - thanks for the help
I'd like to add a feature

The code you suggested sends all the worksheets to word.
Now, I want to send certain worksheets to different word docs, depending on whether a checkbox on a userform is checked.

so, if checkbox1 = true, send ws1,2,3 to word.doc1

For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Next ws

if checkbox2 = true do the same with ws3,4,5 to word.doc2

not sure if the name on the tab of the worksheet matters here

can you kickstart me here?

[fumei]

First of all, are these two different docs open? Or do you have to open them?

However, in any case (whether they are open or you have to open them), I would make each doc a Document object.

BUT... the problem is with:

For Each ws

For...EACH.

You have no logic here for ws 1, 2, 3, and different logic for ws 3, 4, 5. BTW: as you posted, ws 3 seems to go to TWO places?????????

For Each means every one, all of them. So....if this is what you need, then you can not use For Each. Because For Each means all of them.

So...now what? Well, this is - again - just logic. You have to write the logic.

The first thing (logically speaking) is: are checkbox1 and checkbox2 mutually exclusive, or not?

What, EXACTLY, is the situation?

Can you have checkbox1 = True, and checkbox2 = True?

Can you have checkbox1 = True, and checkbox2 = False?

Does checkbox1 = True make checkbox2 = False?

Does checkbox1 = True make checkbox2 = True?

What, EXACTLY, is the connection between them....if any?

Where are you testing?

If you are splitting the worksheets (not using For Each) then you are going to have to identify them. Either by index number, or name. I would suggest, if possible, by name. What if someone shuffled the worksheets? The index number would be changed.

What, exactly, is going to fire the code?

Something like the following may work. UNTESTED!
Dim TheseOnes()
Dim var
Dim j As Long
Select Case checkbox1.Value
Case True
j = 1
TheseOnes = Array("mySheetYadda", _
"mySheetBlah", "mySheetWhatever")
For var = 0 to Ubound(TheseOnes)
Set ws = ActiveWorkbook.Worksheets(TheseOnes(var))
ws.UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Next
Case False
j = 4
TheseOnes = Array("mySheetOtherCrap_B", _
"mySheetBlah_BB", "mySheetWhatever_BB")
For var = 0 to Ubound(TheseOnes)
Set ws = ActiveWorkbook.Worksheets(TheseOnes(var))
ws.UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Next
End Select

If checkbox1 is True, then:

mySheetYadda goes to ExcelHere1
mySheetBlah goes to ExcelHere2
mySheetWhatever goes to ExcelHere3

If it is False, then:

mySheetOtherCrap_B goes to ExcelHere4
mySheetBlah_BB goes to ExcelHere5
mySheetWhatever_BB goes to ExcelHere6

These are mutually exclusive. It could just as easily be NOT mutually exclusive. This is - again - simply a matter of logic, and you have to spec it out precisely.

[johnjuan]

Thanks again fumei

In the end I used four separate IF loops. So that other newbies may see its below.

The 4 checkboxes are not mutually exclusive.

Only thing left is to make it print 4 different docs from the 4 different templates

thanks for all your help

Public Sub CopyWorksheetsToWord3()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check
' the Microsoft Word X.X object library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim ws As Worksheet
Dim j As Long
j = 1
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."

'Set wdApp = New Word.Application (moved inside IF Loops)
'Set wdDoc = wdApp.Documents.Add(Template:= _
'"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\FromExcel2.dot")

'For Each ws In ActiveWorkbook.Worksheets (was to be used for a loop)


'@@@ First Set of Worksheets for K Zoom Appliance @@@

If UserForm1.Controls("Checkbox1").Value = True Then
j = 1

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:= _
"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\KZA.dot")

Sheets.Item("Name & Address").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("Name & Address").UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1 ' was to be used for a loop, is used for "excelhereJ bookmark
Sheets.Item("KZA Overview").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZA Overview").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KZA Implementation").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZA Implementation").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KZA Y2").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZA Y2").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1

End If

'@@@ Second Set of Worksheets for K Zoom Hosted @@@
If UserForm1.Controls("Checkbox2").Value = True Then
j = 1
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:= _
"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\KZH.dot")

Sheets.Item("Name & Address").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("Name & Address").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KZH OV").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZH OV").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KZH Impl").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZH Impl").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KZH Y2").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KZH Y2").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1

End If
'@@@ Second Set of Worksheets for K Classic Hosted @@@
If UserForm1.Controls("Checkbox3").Value = True Then
j = 1
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:= _
"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\KCH.dot")

Sheets.Item("Name & Address").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("Name & Address").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCH OV").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCH OV").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCH Imp").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCH Imp").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCH Y2").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCH Y2").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1

End If
'@@@ Fourth Set of Worksheets for K Classic Appliance @@@
If UserForm1.Controls("Checkbox4").Value = True Then
j = 1
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:= _
"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\KCA.dot")

Sheets.Item("Name & Address").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("Name & Address").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCA OV").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCA OV").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCA Imp").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCA Imp").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1
Sheets.Item("KCA Y2").Select
'Application.StatusBar = "Copying data from " & ws.Name & "..."
Sheets.Item("KCA Y2").UsedRange.Copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
j = j + 1



End If


'@@@ Print Routine ONLY PRINTS ONE SHEET@@@

Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
'wdApp.Quit ' close the Word application
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False

End Sub

[mdmackillop]

Hi John,
While I've not followed all the thread, I would suggest you make use of Arrays and Sub Routines to remove unneccesary repetition. It makes code easier to follow and to maintain. There is no change to your basic logic. Please note this is untested, and may need a bit debugging!

Public Sub CopyWorksheetsToWord3()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check
' the Microsoft Word X.X object library
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim ws As Worksheet
Dim Items

Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
'@@@ First Set of Worksheets for K Zoom Appliance @@@
If UserForm1.Controls("Checkbox1").Value = True Then
Items = Array("ExcelHere", "KZA Overview", "KZA Implementation", "KZA Y2")
Call DoCopy("KZA.dot", Items)
End If
'@@@ Second Set of Worksheets for K Zoom Hosted @@@
If UserForm1.Controls("Checkbox2").Value = True Then
Items = Array("Name & Address", "KZH OV", "KZH Impl", "KZH Y2")
Call DoCopy("KZH.dot", Items)
End If
'@@@ Second Set of Worksheets for K Classic Hosted @@@
If UserForm1.Controls("Checkbox3").Value = True Then
'etc.
End If
'@@@ Fourth Set of Worksheets for K Classic Appliance @@@
If UserForm1.Controls("Checkbox4").Value = True Then
'etc.
End If
'@@@ Print Routine ONLY PRINTS ONE SHEET@@@
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
'wdApp.Quit ' close the Word application
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

Sub DoCopy(TPlate As String, Items)
Dim i, j As Long
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add(Template:= _
"C:\Documents and Settings\jrose\Application Data\Microsoft\Templates\" & TPlate)
For Each i In Items
j = j + 1
Sheets.Item(i).Select
Sheets.Item(i).UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks("ExcelHere" & j).Range.Paste
Next
End Sub

[fumei]

I totally agree that using arrays and subroutines is a MUCH better way to go. md is quite correct, it will make debugging and reading the code much easier...for you, AND for anyone else.

Guys...I know I complain about this but, please use the underscore character when posting code here. Please?

[mdmackillop]

Guys...I know I complain about this but, please use the underscore character when posting code here. Please?
Wrist duly slapped! :yes