excel - 使用 Activex 控件复制 Excel 工作表并使用 VBA 在 word 文档中进行自定义

Question

我正在使用 Excel 中的模板文档布局，该布局具有多个下拉框和文本框、复选框。这是文档具有多个列表数据验证和其他控件的内容。

我能够粘贴带有数据验证列表值的文档，并且我试图让 ativex 控件的值反映在 word 文档中，并将内容正确地放置在 word 文档的边缘。到目前为止，这是我拥有的代码：

 Sub GenerateWordDoc()
 
     ' creates and initializes a word application object
     Dim WordApp As Object
     Set WordApp = CreateObject("Word.Application")
    
     'Makes it visible and adds the blank page into the word app
      WordApp.Visible = True
      WordApp.Activate
      WordApp.Documents.Add
    
     'selects the range that I want to copy
      Range("A1:J52").Copy
    
      'paste the data selected in Excel into the word doc created
       WordApp.Selection.Paste   
      'Saves the file
       WordApp.ActiveDocument.SaveAs2 Environ("userprofile") & "FIlePathTest " &             
      Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx"
       
      'Closes the app
       WordApp.ActiveDocument.Close
    
      'quits the app
       WordApp.Quit
   End Sub

---

我一直在使用以下命令来适应 word 文档中的数据

    Dim WordTable As Object
    Set WordTable = WordApp.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitContent)   '2

---

和

    Set objDoc = objWord.Documents.Add    
    'Paste Table into MS Word
     objDoc.Range.PasteExcelTable _
          LinkedToExcel:=False, _
          WordFormatting:=False, _
          RTF:=False
        
    'Autofit Table so it fits inside Word Document
     Dim WordTable As Object
     Set WordTable = objDoc.Tables(1)
     WordTable.AutoFitBehavior (wdAutoFitWindow)

---

对于下拉框，我有这个代码（注意。这只是一种方法）

 Sub dropdownVariable2()
     Dim dd2 As DropDown
     Dim ddval As Variant
     Set dd2 = ActiveSheet.Shapes("Drop Down 56").OLEFormat.Object
     ddval = dd2.List(dd2.ListIndex)
 End Sub

---

有人可以请主要在我发布的第一种方法中提供建议/帮助。我不知道是不是因为文档不是表格。虽然 Word 将粘贴识别为表格。

标签： excelvbams-wordactivex

Answer 1

例如：

Sub GenerateWordDoc()
Dim ObjWrd As Object, ObjDoc As Object

'Initializes Word
Set ObjWrd = CreateObject("Word.Application")
With ObjWrd
  .Visible = True
  
  'Create a Document
  Set ObjDoc = .Documents.Add
  
  'Copy the Excel Range
  Range("A1:J52").Copy

  With ObjDoc
    'Paste the Excel data into the Document
    .Range.Paste
    
    'Format the Table
    With .Tables(1)
      .AllowAutoFit = True
      .AutoFitBehavior 2 'wdAutoFitWindow
    End With
    
    'Save and close the Document
    .SaveAs Environ("UserProfile") & "FilePathTest " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx", _
      12, , , False '12 = wdFormatXMLDocument
    .Close False
  End With
  
  'Quit Word
  .Quit
End With
End Sub

但是，您在代码中使用 Word 常量表明您已设置对 Word 的引用。在这种情况下，您已经设置为使用早期绑定，这要快得多。使用早期绑定，您可以使用：

Sub GenerateWordDoc()
Dim WrdApp As New Word.Application, WrdDoc As Word.Document

With WrdApp
  .Visible = True
  
  'Create a Document
  Set WrdDoc = .Documents.Add
  
  'Copy the Excel Range
  Range("A1:J52").Copy

  With WrdDoc
    'Paste the Excel data into the Document
    .Range.PasteExcelTable False, False, False
    
    'Format the Table
    With .Tables(1)
      .AllowAutoFit = True
      .AutoFitBehavior wdAutoFitWindow
    End With
    
    'Save and close the Document
    .SaveAs Filename:=Environ("UserProfile") & "FilePathTest " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx", _
      FileFormat:=wdFormatXMLDocument, AddTorecentFiles:=False
    .Close False
  End With
  
  'Quit Word
  .Quit
End With
End Sub