提问人:Bojidar Boiadjiev 提问时间:11/8/2023 更新时间:11/8/2023 访问量:86
Excel中的VBA代码有时有效,但并非总是如此
VBA code in Excel sometimes works but not always
问:
我有一个Excel VBA代码,它试图通过将某些字符串替换为电子表格中的单元格值来编辑Word文档。然后,它将整个Word文档复制到剪贴板中(两页文本,因此不会太长)。 我遇到的问题是,有时它会完美地工作,有时它会执行代码而不会出错,但剪贴板中没有任何内容,有时它会出现“运行时错误'4198':命令失败”,并且错误发生在“for each tagName”循环的随机(不一致)阶段。你能帮我了解它有什么问题吗?
代码:
Private Sub CommandButton1_Click()
Dim wApp As Word.Application
Dim wdoc As Word.Document
Dim ref, savepath As String
'Options.PasteOptionKeepBulletsAndNumbers = True
ref = Cells(2, 4).Value
Set wApp = CreateObject("Word.Application")
wApp.Visible = False
Set wdoc = wApp.Documents.Open(Filename:="G:\Shared drives\Quotes\SWDSandFWDS\SWDS FWDS Quote Template.docx", ReadOnly:=True)
With wdoc
For Each tagName In [tags]
If tagName = "" Then
Exit For
End If
Cells(tagName.Row, 5).Copy
Do While .Application.Selection.Find.Execute(FindText:=tagName, Wrap:=wdFindContinue) = True
If Cells(tagName.Row, 5) = "" Then
.Application.Selection.PasteSpecial DataType:=wdPasteHTML
.Application.Selection.Collapse wdCollapseEnd
Else
.Application.Selection.PasteSpecial DataType:=wdPasteRTF
.Application.Selection.Collapse wdCollapseEnd
End If
Loop
Next tagName
Set myRange = .Content
With myRange.Find
.Text = "[]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll, Forward:=True
End With
'Copy contents of quote to clipboard
.Application.Selection.WholeStory
.Application.Selection.Copy
savepath = "G:\Shared drives\Quotes\SWDSandFWDS\TestOutput\"
.SaveAs2 savepath & ref, _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
.Close
'.Close wdDoNotSaveChanges
End With
'Close instances of Word
Dim objWord As Object
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
' if no active Word is running >> exit Sub
If objWord Is Nothing Then
Exit Sub
End If
objWord.Quit
Set objWord = Nothing
'End Close instances of Word
MsgBox ("Quote coppied to clipboard.")
End Sub
我尝试重新启动所有内容,它似乎在我第一次运行代码时有效,有时第二次运行,但随后失败。
答: 暂无答案
评论
Option Explicit
Cells