时间戳

首页 > 解决方案 > 将 excel 信息移植到 word 时出错 - 编译错误：未定义用户定义的类型，

excel - 将 excel 信息移植到 word 时出错 - 编译错误：未定义用户定义的类型，

问题描述

当我尝试运行此代码时出现编译错误：

（这里只显示了一部分，因为调试器中只突出显示了一个位）

粗体部分是给我带来问题的部分。

我活跃的参考是：

我们在 Word 和 Excel 2016 中运行。

任何帮助将不胜感激。


   'This will delete any rows that have findings that took place prior to the date that was previously entered.
    For CellNum = TotalRowNum To 1 Step -1
        If Cells(CellNum, 10) < backtolong Then Rows(CellNum).Delete
    Next

    CurrentRowNum = Cells(Rows.Count, 2).End(xlUp).Row

    'This sorts the findings remaining based on alphabetical order, to make the copy over to word easier as A is first... etc.
    Range("A1:J" & CurrentRowNum).Sort key1:=Range("I1:I" & CurrentRowNum), order1:=xlAscending, Header:=xlNo

    Dim lastrow As Long
    lastrow = ThisWorkbook.Sheets("Pre-Transfer Table").Range("A1", ThisWorkbook.Sheets("Pre-Transfer Table").Range("A1").End(xlDown)).Rows.Count

    'Designates the file that the data will be transferred to.
    Dim stWordDocument As String

    stWordDocument = InputBox("Please enter the name of the word file you have created for this report (Include '.doc').")

    **Dim wdApp As Word.Application**
    Dim wdDoc As Word.Document
    Dim wdCell As Word.Cell
    Dim i As Long
    Dim j As Long
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaData As Variant

    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Pre-Transfer Table")

    'Creates a 2D array populated by all of the finding critera
    ReDim vaData(1 To lastrow, 1 To 8)

    With wsSheet
        vaData = .Range("A1:H" & lastrow)
    End With

    Set wdApp = New Word.Application

    'Opens the word document by accessing the same folder the workbook is stored in
    Set wdDoc = wdApp.Documents.Open(wbBook.Path & "\" & stWordDocument)

    'Populates the tables with the corresponding criteria
    k = 4
    For i = 1 To lastrow
    j = 0
    For Each wdCell In wdDoc.Tables(k).Columns(2).Cells
        j = j + 1
        wdCell.Range.Text = vaData(i, j)
        Next wdCell
    k = k + 1
    Next i

    'Deletes the sheet used for sorting, as the code cannot run again unless this sheet is removed or the name of it is changed.
    ThisWorkbook.Sheets("Pre-Transfer Table").Delete

    With wdDoc
    .Save
    .Close
    End With

    wdApp.Quit

    'Frees up memory by clearing these variables.
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "Your report has been generated.", vbInformation

End Sub

本节给了我错误：

Sheets("Other Findings").Select
**Range("Table2[ASSIGNED" & Chr(10) & "TO]").Select**
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("E" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
:=False, Transpose:=False

在代码部分

'Creates the sheet where the resorting of data is done. All of the relevant columns are copied over to this table.
Sheets.Add
ActiveSheet.Name = "Pre-Transfer Table"
Sheets("Risk Ranked Findings").Select

Range("Table1[DETAILS]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Range("Table1[LOCATION]").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Application.CutCopyMode = False
Range("Table1[TYPE]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Range("Table1[RECOMMENDED" & Chr(10) & "ACTION]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Range("Table1[DUE DATE]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = True
Selection.NumberFormat = "m/d/yyyy"

Sheets("Risk Ranked Findings").Select
Range("Table1[ASSIGNED" & Chr(10) & "TO]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Range("Table1[RISK]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("I1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Risk Ranked Findings").Select
Range("Table1[FINDING" & Chr(10) & "DATE]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

Sheets("Risk Ranked Findings").Select
Range("S2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("M1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

FindingRowNum = Range("M1")

'This area is where the "Other Findings" are sorted and transferred. There is probably some redundancy here so if anything is to be cleaned up it is
'most likely this.
Dim OtherRowNum As Long
Dim TotalRowNum As Long
Dim CurrentRowNum As Long

Sheets("Other Findings").Select
Range("O2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

OtherRowNum = Range("M2")
TotalRowNum = OtherRowNum + FindingRowNum

Sheets("Other Findings").Select
Range("Table2[DETAILS]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("A" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Other Findings").Select
Range("Table2[LOCATION]").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("B" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Other Findings").Select
Application.CutCopyMode = False
Range("Table2[RECOMMENDED" & Chr(10) & "Action]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("C" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Other Findings").Select
Range("Table2[DUE DATE]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("D" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = True
Selection.NumberFormat = "m/d/yyyy"

Sheets("Other Findings").Select
Range("Table2[ASSIGNED" & Chr(10) & "TO]").Select
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("E" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Other Findings").Select
Range("Table2[TYPE]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("I" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Other Findings").Select
Range("Table2[FINDING" & Chr(10) & "DATE]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pre-Transfer Table").Select
Range("J" & FindingRowNum + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

标签： excelvba

解决方案

推荐阅读