我的代码的目标是在整个word文档中查找书签,并用客户的姓名、地址或评估金额替换这些信息。我的代码搜索包含特定信息的书签(例如,书签看起来像姓名、地址、评估),然后将其替换为excel表格中特定单元格中的信息。我希望代码查看整个文档中的所有书签,并将其替换为适当的信息并保留书签。代码有两个问题。其一,它指出不恰当地使用"Me“(指代码的Me.Repaint部分)。如果我试图在没有Me.Repaint的情况下运行代码,我会得到一个VBA运行时错误‘-2146959355 (80080005)':自动化错误。有没有人有什么建议,我可以做的不同?谢谢
我已尝试删除me.repaint,并尝试重新启动word。我还在VBA参考部分打开了excel对象。
Function Read_Excel_Cell(cellRin As Long) As String
Dim oExcel As Excel.Application
Dim myWB As Excel.Workbook
Set oExcel = New Excel.Application
Set myWB = oExcel.Workbooks.Open("excel file")
Read_Excel_Cell = myWB.Sheets(1).Cells(cellRin, 1)
Set myWB = Nothing
Set oExcel = Nothing
End Function
Sub clientinfoexcel()
Dim bmk As Bookmark
For Each bmk In ActiveDocument.Range.Bookmarks
cltext = bmk.Name
Dim clinfo1 As Range
Set clinfo1 = ActiveDocument.Bookmarks(cltext).Range
If clinfo1.Text Like "*name*" Then
clinfo1.Text = Text: Read_Excel_Cell (1)
ActiveDocument.Bookmarks.Add cltext, clinfo1
ElseIf clinfo1.Text Like "*address*" Then
clinfo1.Text = Text: Read_Excel_Cell (2)
ActiveDocument.Bookmarks.Add cltext, clinfo1
ElseIf clinfo1.Text Like "*appraisal*" Then
clinfo1.Text = Text: Read_Excel_Cell (3)
ActiveDocument.Bookmarks.Add cltext, clinfo1
End If
Next bmk
End Sub
出现自动化错误。
发布于 2019-06-29 07:32:00
设置myWB和oExcel = nothing不会关闭工作簿或退出excel,因此您可能在任务管理器中打开了一堆隐藏的excel实例-签入。
只需一次打开/关闭即可抓取所需的数据。
例如(未测试):
Sub clientinfoexcel()
Dim bmk As bookmark, arrData
Dim clinfo1 As Range, txt As String
arrData = ReadExcelData()
For Each bmk In ActiveDocument.Range.bookmarks
txt = bmk.Range.Text
If txt Like "*name*" Then
SetBookMarkText bmk, arrData(1, 1)
ElseIf txt Like "*address*" Then
SetBookMarkText bmk, arrData(1, 1)
ElseIf txt Like "*appraisal*" Then
SetBookMarkText bmk, arrData(1, 1)
End If
Next bmk
End Sub
'Set the text in a bookmark without destroying it
Sub SetBookMarkText(bmk As bookmark, txt As String)
Dim nm, rng
nm = bmk.Name
Set rng = bmk.Range
rng.Text = txt
rng.Parent.bookmarks.Add nm, rng
End Sub
'return Excel data as a 2-d array
Function ReadExcelData()
Dim oExcel As Excel.Application
Dim myWB As Excel.Workbook
Set oExcel = New Excel.Application
Set myWB = oExcel.Workbooks.Open("excel file")
ReadExcelData = myWB.Sheets(1).Range("A1:A10").Value 'for example
myWB.Close False 'don't save
oExcel.Quit
End Function
https://stackoverflow.com/questions/56813454
复制相似问题