# Using VBA to copy tables from Word to Excel?



## Telloh (May 23, 2006)

Hi everyone!  I am currently trying to get some tables copied from word to excel.  I have some code in word that will copy the tables and put it to a new excel file.  However, I want it to go to sheet 2 of an excel file I already have.  Any help would be appreciated. Here is what i have so far.  

Sub ExtractTablesToExcel()

    Dim oExcelApp As Excel.Application, oExcelWrkBook As Excel.Workbook
    Dim oTbl As Table, oTbls As Tables
    Dim nRows As Integer, rngPaste As Excel.Range, nPasteRow As Integer

    ' create excel workbook
    Set oExcelApp = CreateObject("Excel.Application")
    Set oExcelWrkBook = oExcelApp.Workbooks.Add
    oExcelWrkBook.Application.Visible = True

    nRows = 1
    nPasteRow = 1
    Set oTbls = ThisDocument.Tables

    For Each oTbl In oTbls
        oTbl.Select
        Selection.Copy

        Set rngPaste = oExcelWrkBook.Worksheets(1).Cells(nPasteRow, 1)
        rngPaste.PasteSpecial (xlPasteValues)
        nRows = oTbl.Rows.Count
        nPasteRow = nPasteRow + nRows + 1

    Next oTbl

    oExcelWrkBook.Worksheets(1).Activate

    Set oExcelApp = Nothing
    Set oExcelWrkBook = Nothing
    Set oTbls = Nothing

End Sub


----------



## Telloh (May 23, 2006)

Hmm, came up with a solution.  

Set oExcelWrkBook = oExcelApp.Workbooks.Add  change to:

Set oExcelWrkBook = oExcelApp.Workbooks.Open("path/filename")

If anyone has a different idea / way I would still be glad to hear it.


----------

