excel - Copy from Excel Paste in Word
问题描述
In Excel, I have about 20 sheets with 20 charts in each that I need to copy/paste into Word documents. One Word doc per Excel sheet. I found this article with a solution that I modified to accept a ChartObject as a parameter so that I don't have to think about which chart is being copied. I'm getting the following run-time error on the last line where it calls PasteSpecial in the CopyChart2Word() function:
Which isn't very helpful because it doesn't tell me what is wrong. But the chart is pasted into the Word document with half of the data points missing.
Code:
Public Function moveCharts()
Dim i As Integer
Dim name As String
Dim ChtObj As ChartObject
Dim dummy As Variant
initGlobals
For i = 0 To UBound(employees)
name = employees(i)
For Each ChtObj In Worksheets(name).ChartObjects
dummy = CopyChart2Word(ChtObj)
Next ChtObj
Next i
End Function
Public Function CopyChart2Word(chartObj As ChartObject)
Dim wd As Object
Dim ObjDoc As Object
Dim FilePath As String
Dim FileName As String
FilePath = "C:\Users\name\Desktop"
'Empty document for now
FileName = "Template.docx"
'check if template document is open in Word, otherwise open it
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
Else
On Error GoTo notOpen
Set ObjDoc = wd.Documents(FileName)
GoTo OpenAlready
notOpen:
Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
End If
OpenAlready:
On Error GoTo 0
'find Bookmark in template doc
wd.Visible = True
'ObjDoc.Bookmarks("Bookmark1").Select
'copy chart from Excel
chartObj.chart.ChartArea.Copy
'insert chart to Bookmark in template doc
'wdPasteMetafilePicture didn't work so I used the numeric value 3
'wdInLine didn't work so I used the numeric value 0
wd.Selection.PasteSpecial Link:=False, _
DataType:=3, _
Placement:=0, _
DisplayAsIcon:=False
End Function
Link to sample chart.
解决方案
我怀疑该错误可能是由同时打开的不同 Word 实例引起的。为了消除这种可能性,我建议整理一下您处理 Word 和文档的方式。您的代码逻辑有点混乱。请改用这个。
On Error Resume Next
Set Wd = GetObject(, "Word.Application")
If Err Then Set Wd = CreateObject("Word.Application")
On Error Resume Next
Set ObjDoc = Wd.Documents(Filename)
If Err Then Set ObjDoc = Wd.Documents.Open(FilePath & "\" & Filename)
On Error GoTo 0
我想知道你为什么需要Wd.Visible = True
. 默认情况下它应该是可见的。然而,也许 Window 不是 ActiveWindow。事实上,Word 可能不是活动应用程序。我不认为这对代码很重要。
但这对Selection
对象应该很重要。只有 ActiveWindow 可以有一个Selection
. 因此,如果您打开 Excel 并运行代码,您将无法访问Selection
Word 中的对象。反过来,如果您打开 Word 并进行选择,然后更改为 Excel,则该Selection
对象将丢失。这也可能导致致命错误。只需遵循以下规则:“ Select
VBA 中没有任何内容 [直到代码的最后一行]。
推荐阅读
- matplotlib - 为什么 Matplotlib font_manager 一直匹配可用的字体?
- java - 有没有一种简单的方法可以使用 JavaFX 创建多个 TextField?
- c++ - 这个函数调用应该是模棱两可的吗?
- reactjs - useState 未在第一次渲染时注册
- angular - 是否可以在一个选项卡中有一个模式并在选择另一个选项卡时隐藏?
- c# - 如何区分 + 的输入和输出字符串?
- java - 在 MacOS 上安装 R 并从源代码编译包 - 组件和步骤以使事情正常运行
- reactjs - 如何在 TypeScript 中创建一个让我处理事件的 React 组件?
- sql - 如何计算两个日期之间的月数和天数,而不在 SQL Server 中返回负值
- java - 为什么 JSON 没有被解组到我的 Java 类中?