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.
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.
