excel - 从 Excel 复制并粘贴到 Word - 疑难解答
问题描述
所以我有一个有效的 vba 代码,它在 excel 中获取一堆单元格值,并将它们放在我创建的特定 word 文档中。这些值被放置在我在 word 文档中放置书签的位置。
我的问题是,大约 30-40% 的时间我尝试运行代码,它会冻结并给出错误。当我调试它时,它会显示一行我知道是问题根源的随机代码。我知道我的代码正在运行,因为它会一直运行超过一半的时间而没有问题。有关我的整个代码和错误消息,请参见下文:
Sub ConvertToWord()
Dim WrdDoc As Word.Document
'Reference Word Template
Set WrdDoc = GetObject("R:\Telecom\Structural\Analysis Templates\Mount Analysis\VBA\Automated Report Generation\Mount Analysis Report Template.docm")
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste Cover Page
ActiveDocument.Bookmarks("ReportType").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K14").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
If ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ActiveDocument.Bookmarks("SiteInfo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K18").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
If ThisWorkbook.Worksheets("Word Report Preview").Range("K19").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K19").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ThisWorkbook.Worksheets("Word Report Preview").Range("K20").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K21").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K22").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K23").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ActiveDocument.Bookmarks("Utilization").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("K25").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K25").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("K26").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K26").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("K27").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K27").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ActiveDocument.Bookmarks("Client").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K33").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K34").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K35").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ActiveDocument.Bookmarks("MaserOffice").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K39").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K40").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K41").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ActiveDocument.Bookmarks("Footer").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K46").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ActiveDocument.Bookmarks("FooterSecond").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K49").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste page 2
ActiveDocument.Bookmarks("Objective").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V31").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("DocTable").Select
ThisWorkbook.Worksheets("Word Report").Range("C35:K43").Copy
WrdDoc.ActiveWindow.Selection.Paste
ActiveDocument.Bookmarks("IBC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V9").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("TIA").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V10").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("WS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V11").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("EC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V12").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("RC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V13").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("TF").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V14").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MBE").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V15").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("IWS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V16").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("DIT").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V17").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MWS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V18").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("ML").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V19").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MLL").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V20").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("SN").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V21").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("SC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V22").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("SS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V23").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("S").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V24").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste page 3
ActiveDocument.Bookmarks("Loading").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("C114").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report").Range("N65:T86").Copy
WrdDoc.ActiveWindow.Selection.Paste
If ThisWorkbook.Worksheets("Word Report Preview").Range("D138").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("D138").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ActiveDocument.Bookmarks("AnalysisApproach").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V7").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste page 4
ActiveDocument.Bookmarks("Two").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V26").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("Three").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V27").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("Seven").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V28").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("Eight").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V29").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("Nine").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V30").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste page 5
ActiveDocument.Bookmarks("Results").Select
ThisWorkbook.Worksheets("Word Report").Range("C129:K153").Copy
WrdDoc.ActiveWindow.Selection.Paste
If ThisWorkbook.Worksheets("Word Report Preview").Range("V32").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V32").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ActiveDocument.Bookmarks("Recommendation").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V33").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("V34").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ActiveDocument.Bookmarks("RecommendationOne").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("V35").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V35").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("V36").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V36").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ActiveDocument.Bookmarks("RecommendationTwo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V38").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("V39").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste page 6
ActiveDocument.Bookmarks("MountOne").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V45").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MountTwo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MountThree").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("MountFour").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
ActiveDocument.Bookmarks("ModNote").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
End If
ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
Application.Wait (Now + TimeValue("0:00:01"))
'Select, copy and paste Header
ActiveDocument.Bookmarks("Header").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K56").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K57").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K58").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End Sub
错误信息:
解决方案
这是如何完成的示例:
Sub Tester()
Dim wdApp, fldr, FilePath, doc As Object
Set wdApp = CreateObject("word.application")
wdApp.Visible = True
'open a file from the same folder as the workbook
Set doc = wdApp.documents.Open(ThisWorkbook.Path & "\Template.docx")
SetBookmarkFromCell doc, "Test1", Sheet1.Range("A1") 'no formatting
SetBookmarkFromCell doc, "Test2", Sheet1.Range("A2"), True 'copy formatting
SetBookmarkFromCell doc, "Test3", Sheet1.Range("A3"), True 'copy formatting
End Sub
'Replace the text in a bookmark and optionally copy formatting from the source cell
'Preserves the bookmark
Sub SetBookmarkFromCell(oDoc As Object, sBookmark As String, c As Range, _
Optional CopyFormatting As Boolean = False)
Dim BMRange As Object
If oDoc.Range.Bookmarks.Exists(sBookmark) Then
Set BMRange = oDoc.Range.Bookmarks(sBookmark).Range
With BMRange
.Text = c.Value 'or .Text
If CopyFormatting Then
.Font.Bold = c.Font.Bold
.Font.Italic = c.Font.Italic
.Font.Color = c.Font.Color
If c.Interior.ColorIndex <> xlNone Then
.Shading.BackgroundPatternColor = c.Interior.Color
End If
'add any other formatting properties here...
End If
End With
oDoc.Range.Bookmarks.Add sBookmark, BMRange 'recreates the bookmark
Else
MsgBox "Bookmark '" & sBookmark & "' not found in document '" & oDoc.Name & "'" & _
vbCrLf & "Content not updated"
End If
End Sub
推荐阅读
- gradle - 每次运行构建时如何阻止gradle重新下载
- javascript - Puppeteer 不评估 heroku 上的网络应用程序
- html - 如何使用 Notepad++ 中的正则表达式从多个页面中删除 HTML 代码
- django - Django: as_view: cls 和 initkwargs 如何传递给视图函数
- jsx - 如何在 JSX 中循环的每次迭代都渲染一些东西?
- r - 我图例上的形状似乎与情节 ggplot2 中显示的顺序不同
- vba - 使用 VBA 计划任务检查数字签名是否仍然有效
- javascript - 无法让 NextJS ConfettiExplosion 工作:无效的挂钩调用
- amazon-s3 - 通过 API 网关上传文件的架构
- database - 3 个单独的表格或 1 个带有额外列的表格?