VBA-将Excel表复制到Word-在PC上运行,在Mac上失败 [英] VBA - Copy Excel table to Word - Works on PC, Fails on Mac
问题描述
我编写了一些代码VBA,当单击Excel单元格时,该数据将数据从Excel复制到Word.过程如下:
I have written some code VBA which copies data from Excel to Word when an Excel cell is clicked. The process is as follows:
1. Opens an already created word document, duplicates the word document, and then closes the original document; leaving open the copy (duplicate) for further modification.
2. The code then finds and replaces some placeholder values within the word document with values from the Excel document.
3. The code then deletes 2 different tables (1 at a time), row by row, and replaces each table (1 at a time) with a table copied from Excel.
4. The code then displays a message to the user that all has been completed and exits.
所有代码都可以在我的PC上正常运行,但是在同事的MAC上无法运行,并显示错误:4605-命令不可用",并且在代码的wrdApp.Selection.PasteExcelTable False, False, True
行上失败.
All code works perfectly on my PC, but fails on a colleagues MAC, stating the "Error: 4605 - Command is not available" and failing on the wrdApp.Selection.PasteExcelTable False, False, True
line of code.
代码如下:
Sub Copy2Word()
Dim wrdApp As Object
Dim tempDoc As Word.Document
Dim mrgDoc As Word.Document
Dim NumPay As Integer
Dim cll As Excel.Range
'GET NUMBER OF PAYMENTS SELECTED FOR USE BELOW
NumPay = Notated.Cells(Data.Range("DV2").Value, Data.Range("DV3").Value).Value
'GET LOCATION OF WORD FILE FROM USER
FName = Application.GetOpenFilename
If FName = False Then
usrErr = 1
GoTo ErrHnd
End If
'RECORD THE WORD FILE LOCATION ON HIDDEN SHEET FOR USE BY OTHER MACROS
MergeData.Range("B2").Value = FName
'CREATE WORD OBJECT
On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wrdApp Is Nothing Then
Set wrdApp = CreateObject("Word.Application")
End If
'DISPLAY WORD APPLICATION
On Error Resume Next
wrdApp.Visible = True
wrdApp.Activate
On Error GoTo 0
'OPEN THE (TEMPLATE) FILE
wrdApp.Documents.Open Filename:=FName
'SET A VARIABLE TO REFERENCE ACTIVE DOCUMENT (TEMPLATE)
Set tempDoc = wrdApp.ActiveDocument
'DUPLICATE THE DOCUMENT
wrdApp.Documents.Add wrdApp.ActiveDocument.FullName
'SET A VARIABLE TO REFERENCE THE NEW VERSION OF DOCUMENT
Set mrgDoc = wrdApp.ActiveDocument
'CLOSE THE ORIGINAL (TEMPLATE) VERSION OF DOCUMENT
tempDoc.Close SaveChanges:=False
'ACTIVATE THE NEW DOCUMENT
mrgDoc.Activate
'REPLACE PLACEHOLDER TEXT ITEMS WITH ACTUAL
For Each cll In MergeData.Range(MergeData.Cells(1, 3).Address & ":" & MergeData.Cells(1, MergeData.Range("A1").End(xlToRight).Column).Address)
If cll.Offset(1, 0).Value = "" Then
repTx = cll.Value
Else
repTx = cll.Offset(1, 0).Value
End If
With mrgDoc.Content.Find
.Text = cll.Value
.Replacement.Text = repTx
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With
Next cll
'REPLACE TABLE 2 ON WORD DOC
mrgDoc.Tables(2).Select
For ii = 30 To 2 Step -1
mrgDoc.Tables(2).Rows(ii).Delete
Next ii
wrdApp.Selection.TypeParagraph
'COPY AND PASTE TABLE 1 FROM EXCEL TO WORD
Application.CutCopyMode = False
EO_DOC.Range("EO_TBL_INSCOPE_1").Copy
wrdApp.Selection.PasteExcelTable False, False, True
''''REMAINDER OF CODE AND COMPLETION CONFIRMATION TO USER''''
End Sub
我尝试了很多不同的事情,例如添加DoEvents等,以查看它是否可以解决这种情况,但尚未找到解决方案.
I have tried a bunch of different things such as adding DoEvents, etc. to see if it would remedy the situation but have not yet found a solution.
那里有适用于MAC专家的VBA吗?有人吗?
Any VBA for MAC gurus out there? Anyone?
谢谢.
推荐答案
问题似乎出在wrdApp.Activate的位置,该位置使Excel无法进行复制,而Word无法进行粘贴.在这里,在您的代码的精简版本中,我能够通过移动语句使它位于PasteExcelTable行之前,从而使复制/粘贴"工作.如果必须将行的副本保留在原处,则问题是(例如)在设置CutCopyMode之前立即激活Excel工作簿似乎不足以使Excel正确执行复制". (顺便说一句,我不是Mac VBA Guru!)
The problem seems to be the location of wrdApp.Activate, which prevents Excel from doing the copy and Word from doing the paste. Here, in a cut down version of your code, I was able to make the Copy/Paste work by moving the statement so that it is immediately before the PasteExcelTable line. If you have to leave a copy of the line where it is, the problem is that (for example) Activating the Excel workbook immediately before you set CutCopyMode doesn't appear to be enough to get Excel to do the Copy properly. (BTW I am not a Mac VBA Guru!)
这篇关于VBA-将Excel表复制到Word-在PC上运行,在Mac上失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!