VBA - Run Macro from Word to paste values into Excel

0
votes

I am relatively new to VBA, and am at a loss as to why I cannot paste into Excel from Word. The following macro ends up pasting the value into Word, even though I think I'm activating the Excel document.
The macro is meant to be run from Word; values from various FormFields then need to be pasted into cells in an existing Excel file. I searched for a similar issue, though what was returned seemed to be variations of what I am experiencing, and I could not modify those answers to this. Any help would be appreciated.

Sub Transfer()

Dim WD As Object
Dim ED As Excel.Application
Dim EDS As Excel.Workbook

Set WD = ActiveDocument
Set ED = CreateObject("excel.application")
ED.Visible = True 
Workbooks.Open FileName:= _
"C:\Users\Documents\AppealData.xlsx"
ActiveWorkbook.Activate
Set EDS = ActiveWorkbook

WD.FormFields("AppNum").Copy
EDS.Activate
EDS.Sheets("Sheet1").Range("A1").Select
Selection.Paste

End Sub
2
excelvbams-word
Why don't you run the macro from EXCEL and read the word file and then paste it into EXCEL? – JahKnows
The Word document will be a form that gets filled out; the aim is to have the user who is filling out the Word form run the macro once the form is filled out, to send values for various formfields to Excel for monthly tracking of the Word form. At the end of the month, hopefully, there will be an Excel file with all the Word data. – Cole Gundersen
Ok, can you post up a sample of your word file – JahKnows
Selection.Paste is saying to paste the clipboard to the selection in Word. I think you want something like ED.Selection.Paste. (You should also be careful with Workbooks.Open, which should probably be ED.Workbooks.Open to ensure you are referring to the correct instance of Excel, and likewise ED.ActiveWorkbook instead of just ActiveWorkbook.) A good rule of thumb is "when you have multiple Application objects, qualify everything!" – YowE3K
I posted the code that I have put into the Word file so far, is that not what is needed? – Cole Gundersen

2 Answers

0
votes

Your Selection is referring to the current application. To refer to the Excel application you need to use ED.Selection. But it is a bad idea to rely on Activate and Select anyway.

I suggest you change your code to:

Sub Transfer()

    Dim WD As Document
    Dim ED As Excel.Application
    Dim EDS As Excel.Workbook

    Set WD = ActiveDocument
    Set ED = CreateObject("excel.application")
    ED.Visible = True 
    'Avoid "Activate"
    Set EDS = ED.Workbooks.Open(FileName:= _
             "C:\Users\Documents\AppealData.xlsx")

    WD.FormFields("AppNum").Copy
    'Avoid "Activate" and "Select" and "Selection"
    '"Paste" is a worksheet Method, use "PasteSpecial" for a Range
    'Use "xlPasteValues" to avoid formatting issues
    EDS.Sheets("Sheet1").Range("A1").PasteSpecial Excel.xlPasteValues
End Sub
0
votes

This here should work for you. 

Sub Transfer()
   Dim oExcel As Excel.Application
   Dim oWB As Workbook

   Set oExcel = New Excel.Application
   Set oWB = oExcel.Workbooks.Open("C:\Users\Documents\AppealData.xlsx")

   oExcel.Visible = True

   Workbooks("Book1").Worksheets("Sheet1").Cells(1, 1).Value = _ 
      CStr(Documents("Document1").FormFields("AppNum").Result)
End Sub