VBA从Excel到Word插入注释 [英] VBA Inserting Comments from Excel to Word

问题描述

我是VBA的新手，我很难将Excel中的数据中的注释插入到Word文档中.我试图用Word编写VBA，并希望它从单独的电子表格中提取数据

I'm new to VBA and I'm having difficulty trying to insert comments from data that I have in Excel onto a Word document. I am trying to write the VBA in Word and want it to extract data from a separate spreadsheet

Sub ConvertCelltoWordComment()

Dim Rng As Range
Dim wApp As Object
Dim strValue As String
Dim xlapp As Object
Dim xlsheet As Object
Dim xlbook As Object

'Opens Excel'

    Set xlapp = GetObject("C:\Users\eugenechang\Desktop\...xlsx")

If Err Then
     Set xlapp = CreateObject("Excel.Application")
End If

On Error GoTo 0

Dim i As Integer

For i = 1 To 5
    With xlsheet
        strValue = ActiveSheet.Cells(i, 1).Offset(1, 0)
    End With
    'Insert comment into document'

    ActiveDocument.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="15"
    ActiveDocument.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=5
    ActiveDocument.Comments.Add Range:=Selection.Range, Text:=strValue
Next i

End Sub

我正在尝试使其正常工作，但是它给我一个错误对象未定义".我尝试在"With xlsheet"下面的strValue行中设置一个对象，但是碰到了墙.有帮助吗?

I'm trying to get it to work, but it is giving me an error "Object not defined". I've tried setting up an object within the strValue line below "With xlsheet", but am hitting a wall. Any help??

推荐答案

您尚未为xlsheet分配任何内容-因此(默认情况下)它等于Nothing.

You have not assigned anything to xlsheet - so this (by default) equates to Nothing.

尝试将xlSheet设置为有意义的内容.以下仅是示例:

Try setting xlSheet to something meaningful. The following is only an example:

For i = 1 To 5
    Set xlsheet = xlbook.Worksheets(i) ' <--- example here
    With xlsheet
        strValue = .Cells(i, 1).Offset(1, 0) '<-- don't use ActiveSheet
    End With
    'Insert comment into document'

    ActiveDocument.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="15"
    ActiveDocument.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=5
    ActiveDocument.Comments.Add Range:=Selection.Range, Text:=strValue
Next I

这里重要的一点是，您还没有设置xlbook-您 必须 还要为xlbook分配有意义的内容.

An important note here is that you also have not set xlbook - you must also assign something meaningful to xlbook.

这篇关于VBA从Excel到Word插入注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！