Copy Worksheets to Word

[PBnJ]

I have Excel workbooks with 10-50 sheets in each and I would like to be able to save or open them in Word. Since I've upgraded to Office 2003 I no long have the option to do that via the software open options. Looking around on the 'net I found a macro that says it'll do just what I'd like but when I use it I get an error saying the User Application is not defined.

Can someone tell me what exactly the error means and why it's stoping the macro from working?

Here's the full macro:

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
    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 = wdNormalView
        Else
            .View.Type = wdNormalView
        End If
    End With
    Set wdDoc = Nothing
    wdApp.Visible = True
    Set wdApp = Nothing
    Application.StatusBar = False
End Sub

Here is the line that the Debugger flags:

Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet

All help is very appreciated!!

[Charlize]

It's early binding versus late binding. You could try this :

Sub CopyWorksheetsToWord()
'Use an object instead
'Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Dim wdApp As Object
Dim wdDoc As Object
Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating new document..."
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add
    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 = wdNormalView
        Else
            .View.Type = wdNormalView
        End If
    End With
    Set wdDoc = Nothing
    wdApp.Visible = True
    Set wdApp = Nothing
    Application.StatusBar = False
End Sub

[PBnJ]

Hi Charlize! And thanks for the help. :D

I gave this a try and received a couple runtime errors, I took out the lines that were giving me trouble, such as creating page breaks; useful but not required.

Now I've encountered a different type of problem. In the Excel document I have some cells in Column A merged, in most of the pages this carries over with the pasting macro but about half way through it stops, is ok for one worksheet/page, and then stops again.

Would you happen to know what causes this? I've ran the macro several times and it's consistantly wrong on the same worksheets. Is it something in my Excel worksheets, formatting, etc since it works on some pages and not others?