使用VBA代码将表格从Excel复制到Word书签 [英] Copying table from Excel to Word bookmark using VBA code

问题描述

我实际上需要您的帮助来修改此宏代码.我从其他地方获得了此代码(对不起，忘了他的名字！).基本上，我有来自Excel的Sheet1的表数据源.我需要传输此表，并使用我的书签"bookmark"将边框，字体大小，自动调整等格式保留到MS Word. 注意事项:

I actually need your help on modifying this macro code. I got this code from somewhere else (sorry, forgot his name!). Basically, I have table data source from Sheet1 of excel. I need to transfer this table, keeping the formatting like borders, font size, auto fit, etc to MS Word with my bookmark named 'bookmark'. Something to note:

1. 书签的位置在最后一个段落"中.我的Word文件中有12个以上的段落，但是表格应该放在最后一个段落中.
2. 请注意，表数据源中的行和列数是动态的.这意味着行数和列数不是固定的.它可以改变.我现有的宏代码适用于此目的.但是，它完全删除了现有段落.该表取代了我文件中的所有内容.我不知道将这张表放到书签所在位置的确切宏代码.

感谢您对第2项的帮助.下面是代码:

Appreciate your help on item 2. Below is the code:

Private Sub CommandButton1_Click()
On Error Resume Next

    ' FIRST GET THE ROWS COLUMNS OF A USED RANGE.

    Dim iTotalRows As Integer   ' GET TOTAL USED RANGE ROWS.
    iTotalRows = Worksheets("sheet1").UsedRange.Rows.Count


    Dim iTotalCols As Integer   ' GET TOTAL COLUMNS.
    iTotalCols = Worksheets("sheet1").UsedRange.Columns.Count



    ' WORD OBJECT.
    Dim oWord As Object
    Set oWord = CreateObject(Class:="Word.Application")
    oWord.Visible = True
    oWord.Activate

    ' ADD A DOCUMENT TO THE WORD OBJECT.
    Dim oDoc
    Set oDoc = oWord.Documents.Open("C:\Macro\samplebookmark1.docx")


    ' CREATE A RANGE FOR THE TABLE INSIDE WORD DOCUMENT.
    Dim oRange
    Set oRange = oDoc.Range


    ' CREATE AND  DEFINE TABLE STRUCTURE USING
        ' THE ROWS AND COLUMNS EXTRACTED FROM EXCEL USED RANGE.
    oDoc.Tables.Add oRange, iTotalRows, iTotalCols


    ' CREATE A TABLE OBJECT.
    Dim oTable
    Set oTable = oDoc.Tables(1)
    oTable.Borders.Enable = True      ' YES, WE WANT BORDERS.


    Dim iRows, iCols As Integer


    ' LOOP THROUGH EACH ROW AND COLUMN TO EXTRACT DATA IN EXCEL.
    For iRows = 1 To iTotalRows
        For iCols = 1 To iTotalCols
            Dim txt As Variant
            txt = Worksheets("Sheet1").Cells(iRows, iCols)
            oTable.cell(iRows, iCols).Range.Text = txt        ' COPY (OR WRITE) DATA TO THE TABLE.


            ' BOLD HEADERS.
            If Val(iRows) = 1 Then
                objTable.cell(iRows, iCols).Range.Font.Bold = True
            End If
        Next iCols
    Next iRows

    Set oWord = Nothing
End Sub

推荐答案

问题在此行内:

Set oRange = oDoc.Range

现在，它定义了整个文档范围，而不是书签的范围.
您应该使用类似这样的东西:

As it is now, it defines whole document range instead of bookmark's range.
You should use something like:

Set oRange = oDoc.Bookmarks("BookmarkName").Range

---

关于您的新问题，解决此问题的一种方法是将表直接放入文档的最后一段而不使用书签.

---

Concerning your new problem, one way to fix it is to put table directly into document's last paragraph without using a bookmark.

Set oRange = oDoc.Range.Paragraphs(oDoc.Range.Paragraphs.Count).Range

这篇关于使用VBA代码将表格从Excel复制到Word书签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！