run VBA from Word and copy all Worksheets of an Excel file to Word

[RCGUA]

I need to run this code from Word (not Excel). The code below opens a
dialog and lets the user pick the Excel file to open and then begins
to cycle through the Worksheets. I have tried everything but I can't
find the correct syntax to copy the Excel Worksheet. I need to copy
the entire worksheet to a page in Word. The Excel file (WorkBook) has
many individual tabs/Worksheets, I need to copy each Worksheet onto a
new page in Word. Does anyone know how to do this?

Sub GetExcelFromWord()


Const Error_FileNotFound = 1004
Const Error_NotRunning = 429
Const Error_NotInCollection = 9
Dim fileName As String
Dim wkbName As String
Dim xlApp As Object
Dim wkBook As Object
Dim emptyFound As Boolean
Dim endRow As Boolean
Dim x As Long
Dim i As Long
Dim j As Long
Dim k As Integer

With Application.FileDialog(msoFileDialogFilePicker)
MsgBox "Select the Excel file you want to get the data from.",
vbOKCancel
If .Show Then
fileName = .SelectedItems(1)
Else
MsgBox "You didn't select an Excel file to open."
Exit Sub
End If
End With
k = InStrRev(fileName, "\", -1, vbTextCompare)
If k > 0 Then
wkbName = Right(fileName, k - 1)
Else
MsgBox "A suitable file was not selected."
Exit Sub
End If


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = Error_NotRunning Then
Set xlApp = CreateObject("Excel.Application")
MsgBox "A new instance of Excel was created."
Else
MsgBox "An open instance of Excel is being used."
End If
On Error GoTo 0
xlApp.Visible = True


On Error Resume Next
Set wkBook = xlApp.Workbooks(wkbName)
If Err.Number = Error_NotInCollection Then
Err.Clear
Set wkBook = xlApp.Workbooks.Open(fileName)
If Err.Number = Error_FileNotFound Then
MsgBox "The file specified could not be opened.", _
vbCritical Or vbOKOnly, "File Not Opened"
Set xlApp = Nothing
Exit Sub
End If
End If
wkBook.Activate
' On Error GoTo 0

For x = 1 To wkBook.WorkSheets.Count
With wkBook.WorkSheets(x)

'## This is where I need to copy the Excel Spreadsheet, ###

End With
Next
Set xlApp = Nothing
End Sub

 

[RCGUA]

I need to run this code from Word (not Excel).  The code below opens a
dialog and lets the user pick the Excel file to open and then begins
to cycle through the Worksheets.  I have tried everything but I can't
find the correct syntax to copy the Excel Worksheet.  I need to copy
the entire worksheet to a page in Word.  The Excel file (WorkBook) has
many individual tabs/Worksheets, I need to copy each Worksheet onto a
new page in Word.  Does anyone know how to do this?

Sub GetExcelFromWord()

    Const Error_FileNotFound = 1004
    Const Error_NotRunning = 429
    Const Error_NotInCollection = 9
    Dim fileName As String
    Dim wkbName As String
    Dim xlApp As Object
    Dim wkBook As Object
    Dim emptyFound As Boolean
    Dim endRow As Boolean
    Dim x As Long
    Dim i As Long
    Dim j As Long
    Dim k As Integer

    With Application.FileDialog(msoFileDialogFilePicker)
MsgBox "Select the Excel file you want to get the data from.",
vbOKCancel
        If .Show Then
            fileName = .SelectedItems(1)
        Else
            MsgBox "You didn't select an Excel file to open."
            Exit Sub
        End If
    End With
    k = InStrRev(fileName, "\", -1, vbTextCompare)
    If k > 0 Then
        wkbName = Right(fileName, k - 1)
    Else
        MsgBox "A suitable file was not selected."
        Exit Sub
    End If

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number = Error_NotRunning Then
        Set xlApp = CreateObject("Excel.Application")
        MsgBox "A new instance of Excel was created."
    Else
        MsgBox "An open instance of Excel is being used."
    End If
    On Error GoTo 0
    xlApp.Visible = True

    On Error Resume Next
    Set wkBook = xlApp.Workbooks(wkbName)
    If Err.Number = Error_NotInCollection Then
         Err.Clear
         Set wkBook = xlApp.Workbooks.Open(fileName)
         If Err.Number = Error_FileNotFound Then
              MsgBox "The file specified could not be opened.", _
                  vbCritical Or vbOKOnly, "File Not Opened"
              Set xlApp = Nothing
              Exit Sub
          End If
    End If
    wkBook.Activate
 '   On Error GoTo 0

    For x = 1 To wkBook.WorkSheets.Count
        With wkBook.WorkSheets(x)

'## This is where I need to copy the Excel Spreadsheet, ###

    End With
Next
    Set xlApp = Nothing
End Sub

I figured out the correct syntax to copy and the code below works
fine, however, each Worksheet in pasted into a new Word doc. Any
ideas on how to pasted each Excel Worksheet into a new *page* in the
same Word doc?

 

[Pesach Shelnitz]

Hi,

You talk about "the code below", but you didn't include it in your message.