当前位置: 动力学知识库 > 问答 > 编程问答 >

Word 2010 vba to APPEND table to Excel file

问题描述:

I have a Word 2010 vba script which is meant to take the contents of a table and append it to an Excel 2010 file. I have the code almost complete but am struggling to get the insertion point moved to the next empty row in excel.

The idea is that the 2nd and 3rd etc time the macro is run the data is appended into the Excel file underneath the last lot of data. I'm struggling to reference the last excel row used from Word to make that the insertion point.

Here is the code I have so far:

Sub ExportBookmarksToExcel()

Dim myTable As Table

Dim RowsCount As Integer

Dim ColumnsCount As Integer

Dim oExcel As Object

Set oExcel = CreateObject("Excel.Application")

Dim oExcel1 As Object

Set oExcel1 = oExcel.Workbooks.Open("C:\Users\richard\Desktop\DummyFolder\GettingFieldsFromWordToExcelDemo\Exported.xlsm")

For Each myTable In ActiveDocument.Tables

Dim WS As Object

Set WS = oExcel1.ActiveSheet

RowsCount = myTable.Rows.Count

ColumnsCount = myTable.Columns.Count

MsgBox oExcel1.CurrentWorkbook.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

For i = 3 To RowsCount

For j = 1 To ColumnsCount

WS.Cells(i, j) = myTable.Cell(i, j)

Next j

Next i

Next myTable

oExcel1.Close (True) 'Closes the workbook by saving changes.

Set oExcel1 = Nothing

Set oExcel = Nothing

ActiveDocument.Repaginate

End Sub

网友答案:

there are three issues is in:

oExcel1.CurrentWorkbook.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

1) Excel Model Object has no CurrentWorkbook property for Application object

2) there is no ActiveSheet

since Word is your client application and Excel is the server one, you don't have such things as ActiveWorkbook or ActiveSheet qualifications implicitly assumed should your excel range references lack a workbook and/or worksheet qualifications

this means that Rows.Count means nothing in your context

3) xlUp is an Excel enumeration name

since you're using late binding you don't have any Excel Application enumerations (like XlDirection) available, so you have to use their real names value (like -4162 for xlUp)

that said you code can be rewritten as follows:

Option Explicit

Sub ExportBookmarksToExcel()
    Dim myTable As Table
    Dim oExcel As Object
    Dim oExcelWb As Object
    Dim lastRow As Long, i As Long, j As Long

    Set oExcel = CreateObject("Excel.Application")
    Set oExcelWb = oExcel.Workbooks.Open("C:\Users\richard\Desktop\DummyFolder\GettingFieldsFromWordToExcelDemo\Exported.xlsm")

    With oExcelWb.ActiveSheet '<-- reference open workbook active sheet
        For Each myTable In ActiveDocument.Tables
            lastRow = .Cells(.Rows.count, 1).End(-4162).Offset(1, 0).Row '<-- get current column A last non empty cell row
            For i = 3 To myTable.Rows.count
                For j = 1 To myTable.Columns.count
                    .Cells(lastRow + i - 3, j) = myTable.Cell(i, j)
                Next j
            Next i
        Next myTable
    End With

    oExcelWb.Close True 'Closes the workbook by saving changes.
    Set oExcelWb = Nothing
    oExcel.Quit
    Set oExcel = Nothing
    ActiveDocument.Repaginate
End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: