I've created a macro to move information from Excel into existing tables in a Word document. When I try to compile, I'm getting a "Compile error: variable not defined" error on wdCollapseStart within the statement Selection.Collapse Direction:=wdCollapseStart. I simplified to Selection.Collapse, but then I got the same error on the next line with wdRow. wdCollapse and wdRow are not variables, so I don't understand why I'm getting this error, especially when the exact same statement functions perfectly if I run it through the immediate window from Word. To clarify, I've written this macro for Excel, but the macro has to manipulate a Word document as well.
我创建了一个宏来将信息从Excel移动到Word文档中的现有表中。当我尝试编译时,我在语句Selection.Collapse Direction:= wdCollapseStart中的wdCollapseStart上收到“编译错误:变量未定义”错误。我简化为Selection.Collapse,但后来我在wdRow的下一行得到了同样的错误。 wdCollapse和wdRow不是变量,所以我不明白为什么我会收到这个错误,特别是当完全相同的语句完全运行时,如果我通过Word的即时窗口运行它。为了澄清,我已经为Excel编写了这个宏,但宏也必须操作Word文档。
Full code below (the offending statement is in the first with statement):
下面的完整代码(违规声明在第一个声明中):
Option Explicit
Dim Word_App As Object
Dim Word_Doc As Object
Sub IDA_Creator()
Dim Starting_Sheet As Integer: Starting_Sheet = 4
Dim Ending_Sheet As Integer: Ending_Sheet = Worksheets.Count - 1
'Activates first category worksheet
Worksheets(Starting_Sheet).Activate
'Get the name of the destination Word file
Dim IDA_Word As String
IDA_Word = Application.GetOpenFilename(FileFilter:="Word Files (*.DOCX), *.DOCX", Title:="Select File To Be Opened")
'Open Word
Set Word_App = CreateObject("Word.Application")
Word_App.Visible = True
Set Word_Doc = Word_App.Documents.Open(IDA_Word)
Dim i As Integer
For i = 1 To 3
Call TrimTable
Call Move_To_Table(Starting_Sheet, i)
Dim j As Integer
For j = Starting_Sheet To Ending_Sheet
Call Copy_To_Word(i)
'Prepares Word table for next Category (unless it's the last category
If ActiveSheet.Index > Worksheets.Count - 1 Then
ActiveSheet.Next.Select
With Word_Doc
Selection.InsertRowsBelow (2)
Selection.Collapse Direction:=wdCollapseStart
Selection.EndOf Unit:=wdRow, Extend:=wdExtend
Selection.Cells.Merge
Selection.Collapse Direction:=wdCollapseStart
End With
End If
Next j
Worksheets(Starting_Sheet).Activate
Next i
'Save Word Document and Close Word
With Word_Doc
.Save
End With
Word_App.Quit
Set Word_Doc = Nothing
Set Word_App = Nothing
End Sub
'Trims the table to the first category and criterion
Function TrimTable()
With Word_Doc
With Word_App.Selection.Find
.Text = "Table " & i
.Style = "Caption"
.Execute
End With
Selection.MoveDown Count:=4
Selection.EndOf Unit:=wdTable, Extend:=wdExtend
Selection.Rows.Delete
End With
End Function
'Moves the cursor to the first cell of the next table, formats table with correct style
Function Move_To_Table(Starting_Sheet As Integer, i As Integer)
Sheets(Starting_Sheet).Activate
With Word_Doc
With Word_App.Selection.Find
.Text = "Table " & i
.Style = "Caption"
.Execute
End With
'Ensures first row of table is in "CellHeadingL" style
Selection.MoveDown
Selection.EndOf Unit:=wdRow, Extend:=wdExtend
Selection.Style = "CellHeadingL"
Selection.Collapse Direction:=wdCollapseStart
'Clears boilerplate text
Selection.MoveDown
Selection.EndOf Unit:=wdTable, Extend:=wdExtend
Selection.Delete
Selection.Collapse Direction:=wdCollapseStart
End With
End Function
'Copies criteria text from Excel to Word
Function Copy_To_Word(i As Integer)
'Copies category name from Excel
Selection.Copy
'Pastes category name into Word
With Word_Doc
Selection.EndOf Unit:=wdRow, Extend:=wdExtend
Selection.PasteSpecial DataType:=wdPasteText
Selection.Style = "CellBodyL"
Selection.Font.Bold = True
Selection.MoveDown
End With
'Determines number of subcategories and criteria
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Dim Number_Criteria As Integer: Number_Criteria = Selection.Rows.Count - 2
'Adds correct number of rows to Word table, positions cursor at top row
With Word_Doc
Selection.InsertRowsBelow (Number_Criteria - 1)
Selection.Collapse Direction:=wdCollapseStart
Selection.MoveUp
End With
'Selects the first subcategory or criterion from Excel and copies to clipboard
Selection(1).Select
Selection.Offset(2, 0).Select
'Begins copying to Word
For i = 1 To Number_Criteria
Selection.Copy
'Checks whether there's a subcategory (merged) cell in Excel, formats Word table to match and pastes subcategory into Word
If ActiveCell.MergeCells = True Then
With Word_Doc
Selection.EndOf Unit:=wdRow, Extend:=wdExtend
Selection.Cells.Merge
Selection.PasteSpecial DataType:=wdPasteText
Selection.Style = "CellBodyL"
Selection.Font.Bold = True
End With
'Excel cell isn't a subcategory (merged) cell in Excel, paste criterion into Word
Else
With Word_Doc
Selection.PasteSpecial DataType:=wdPasteText
Selection.Style = "CellBodyL"
End With
End If
'Moves to next cell
Selection.Offset(1, 0).Select
Next i
'Puts cursor in position for next pass (Preferred or Optional criteria)
Selection.Offset(5, 0).Select
End Function
Any help would be appreciated.
任何帮助,将不胜感激。
1 个解决方案
#1
3
Excel VBA doesn't know about predefined Word constants such as wdCollapseStart. According to this wdCollapseStart is equal to 1. One solution is to just directly use the numbers that such constants refer to (using the linked-to documentation). A second solution is to add a reference to Microsoft Word 14.0 Object Library (under Tools/References in the VBA editor) after which your code should work without modification.
Excel VBA不了解预定义的Word常量,例如wdCollapseStart。根据这个wdCollapseStart等于1.一个解决方案是直接使用这些常量引用的数字(使用链接到文档)。第二种解决方案是添加对Microsoft Word 14.0对象库的引用(在VBA编辑器中的工具/引用下),之后您的代码应该无需修改即可运行。
#1
3
Excel VBA doesn't know about predefined Word constants such as wdCollapseStart. According to this wdCollapseStart is equal to 1. One solution is to just directly use the numbers that such constants refer to (using the linked-to documentation). A second solution is to add a reference to Microsoft Word 14.0 Object Library (under Tools/References in the VBA editor) after which your code should work without modification.
Excel VBA不了解预定义的Word常量,例如wdCollapseStart。根据这个wdCollapseStart等于1.一个解决方案是直接使用这些常量引用的数字(使用链接到文档)。第二种解决方案是添加对Microsoft Word 14.0对象库的引用(在VBA编辑器中的工具/引用下),之后您的代码应该无需修改即可运行。