Word Macro to Copy Table to new Excel file

[scottb80]

I am a pretty good Excel VBA programmer but have very little experience in Word. I now have the need to have a macro in a Word document that will copy and entire table into a new Excel workbook. If I had some base code to work with, I'm sure I could figure it out, but what I would like it to do is to:
1. Since there are multiple tables in the Word Doc. query the user for which one they want to export.
2. Once the table is selected, open a new Excel workbook, then query what sheet and cell to paste it to.
3. Ask the user if they want to do another table.

If you have some code pieces that will do this I'd appreciate if you could share them. Thanks!

Sorry, forgot to mention, this is Word and Excel 2007.

[HansV]

Here is an example. The user should click inside a table before running the macro.
When I run the macro, Excel gets the focus but it is possible that this won't happen on all computers - this is difficult to manage since Windows XP and later sometimes prevent other applications from "stealing the focus".
The user will have to switch back to Word and start the macro again.
The code uses late binding, so no reference to the Excel object library is necessary.

Code: Select all

Sub Copy2XL()
  Dim tbl As Table
  Dim xlApp As Object
  Dim xlWbk As Object
  Dim xlWsh As Object
  Dim xlRng As Object

  If Not Selection.Information(wdWithInTable) Then
    MsgBox "Please click inside a table and try again.", vbExclamation
    Exit Sub
  End If

  On Error Resume Next
  Set xlApp = GetObject(, "Excel.Application")
  If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
      MsgBox "Can't start Excel.", vbCritical
      Exit Sub
    End If
  End If
  On Error GoTo ErrHandler

  xlApp.Visible = True
  Set xlWbk = xlApp.Workbooks.Add(-4167) ' xlWBATWorksheet
  Set xlWsh = xlWbk.Worksheets(1)
  Set xlRng = xlApp.InputBox(Prompt:="Please select destination", _
    Type:=8)
  If xlRng Is Nothing Then
    MsgBox "You didn't select a destination...", vbInformation
    Exit Sub
  End If
  Selection.Tables(1).Range.Copy
  xlWsh.Paste Destination:=xlRng
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
End Sub

[scottb80]

Hans,
That's great, it gives me a good base to work from. Thank you again.

Scott