[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I copy data from MS Excel to a Worksheet Object in a existing MS Word file using VBA?

Posted on 2007-07-30
21
Medium Priority
?
309 Views
Last Modified: 2010-04-30
How do I copy data from MS Excel to a Worksheet Object in a existing MS Word file using VBA?  I am able to open the MS Word file, however, I have been unsuccessful in opening the worksheet object in MS Word  to copy my budget data from MS Excel.  I would also like to save and close the Word.doc after the update is complete.
0
Comment
Question by:tlbdst
  • 11
  • 7
  • 3
21 Comments
 
LVL 13

Expert Comment

by:Ryan
ID: 19592233
Public Sub pastetest()
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add

ActiveSheet.Range("A1:D10").Copy
wdDoc.Range.InsertParagraphAfter
wdDoc.Range.Paste

Set wdDoc = Nothing
Set wdApp = Nothing
End Sub


0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19592305
Is there a reason you don't simply link the budget sheet into the Word document?
Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19592520
Yes, the requirement precludes links.  I realize links can be active or turned off.  However, I am trying to avoid this mess for ease of continuity purposes.  The current requirement requires no link or formulas in the worksheet object in the word.doc.  The format for the worksheet object is set in word, I do not want to recopy from excel a new worksheet object, I just want to copy the data from excel into the existing worksheet object in word.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:tlbdst
ID: 19592688
MrBullWinkle: The application works but does not do what I'm looking for... I do not want to open a new word.doc.  I want to open an existing word doc and then open a specific worksheet object within that word.doc and then copy the data from excel into that worksheet object.
0
 
LVL 13

Expert Comment

by:Ryan
ID: 19593697
I've looked all over. I can open the word file, copy and paste in values from excel, but i can't see where to manipulate worksheets in word.  

It does look like from the word.document variable you can access field(0). But everything I try for that item fails.

rorya might have the idea, just use the linking. If necessary create a worksheet just for the link that you update then update the link to word.
0
 
LVL 13

Expert Comment

by:Ryan
ID: 19593702
This is what I got to

Public Sub pastetest()
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document

wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(ActiveWorkbook.Path & "\test.doc")

ActiveSheet.Range("A1:D10").Copy

wdDoc.Fields(0). 'nothing seems to work here
wdDoc.Range.InsertParagraphAfter
wdDoc.Range.Paste

Set wdDoc = Nothing
Set wdApp = Nothing
End Sub
0
 

Author Comment

by:tlbdst
ID: 19594081
Searching for anyone that can answer this question...

How do I copy data from MS Excel to a Worksheet Object in a existing MS Word file using VBA?  The VBA code should first open the existing word.doc.  Once in the word.doc it should update the worksheet object in the word.doc from the data I copy from excel.  I would also like to save and close the Word.doc after the update is complete.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 19600754
Something like this should get you started:

Sub EditOLE()
   Dim objOLE As Object
   Dim appWord As Object
   Dim docWord As Object
   Dim shp As Object
   Selection.Copy
   Set appWord = CreateObject("Word.Application")
   Set docWord = appWord.documents.Open("C:\Test\testdoc1.doc")
   appWord.Visible = True
   Set shp = docWord.inlineshapes(1)
   shp.Activate
   Set objOLE = shp.OLEFormat.Object
   objOLE.Worksheets(1).Range("A1").PasteSpecial xlPasteAll
   docWord.Close True
   appWord.Quit
End Sub

Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19601023
Okay, were getting closer.  The routine will open my word.doc and copy the data from excel into the worksheet object in my word.doc.  However, it copies the data as a picture on top of the cells in the worksheet object.  Is there anyway we can get it to copy the data to the cells?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19601113
What are you copying? That code copies cells to cells in my tests (Office XP, incidentally)
Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19601178
Office 2003.  Ran it again... the numbers are definately pasted in as a picture... but I do need the cells to cells.  Code I used:

Sub EditOLE()
   Dim objOLE As Object
   Dim appWord As Object
   Dim docWord As Object
   Dim shp As Object
   Selection.Copy
   Set appWord = CreateObject("Word.Application")
   Set docWord = appWord.Documents.Open("C:\Tom Brown\TestWord.doc")
   appWord.Visible = True
   ActiveSheet.Range("A1:D10").Copy
   Set shp = docWord.InlineShapes(1)
   shp.Activate
   Set objOLE = shp.OLEFormat.Object
   objOLE.Worksheets(1).Range("A1").PasteSpecial xlPasteAll
   'docWord.Close True
   'appWord.Quit
End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19601499
I'll have to test again on Office 2003 when I am at home, but in the meantime, if you only need the data, you might try putting the selection.Value into a variant and then assigning that to the corresponding area in the word doc worksheet.
0
 

Author Comment

by:tlbdst
ID: 19603950
I wish I new what that ment... you guys are amazing with this stuff.  Please, let me know if you are able to get it working from home on your Office 2003.

Respectfully,
Tom
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19604698
See if this works:

Sub EditOLE()
   Dim objOLE As Object
   Dim appWord As Object
   Dim docWord As Object
   Dim shp As Object
   Dim varData
   varData = Selection.Value
   Set appWord = CreateObject("Word.Application")
   Set docWord = appWord.Documents.Open("C:\Tom Brown\TestWord.doc")
   appWord.Visible = True
   ActiveSheet.Range("A1:D10").Copy
   Set shp = docWord.InlineShapes(1)
   shp.Activate
   Set objOLE = shp.OLEFormat.Object
   With objOLE.Worksheets(1)
      .Range(.Cells(1, 1),.Cells(Ubound(varData,1), Ubound(varData, 2))).Value = varData
   End With
   'docWord.Close True
   'appWord.Quit
End Sub

Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19607932
Nope.  Still pastes a picture into the worksheet object.  Also produces a run-time error '13': type mismatch for the line of code:   .Range(.Cells(1, 1),.Cells(Ubound(varData,1), Ubound(varData, 2))).Value = varData

Over...
0
 

Author Comment

by:tlbdst
ID: 19607986
rorya:  Your first solution does work.  I copied a new worksheet object from a new excel sheet into the work doc and it worked.  Thanks!
v/r,
Tom
Sub EditOLE()
   Dim objOLE As Object
   Dim appWord As Object
   Dim docWord As Object
   Dim shp As Object
   Selection.Copy
   Set appWord = CreateObject("Word.Application")
   Set docWord = appWord.documents.Open("C:\Test\testdoc1.doc")
   appWord.Visible = True
   Set shp = docWord.inlineshapes(1)
   shp.Activate
   Set objOLE = shp.OLEFormat.Object
   objOLE.Worksheets(1).Range("A1").PasteSpecial xlPasteAll
   docWord.Close True
   appWord.Quit
End Sub
0
 

Author Comment

by:tlbdst
ID: 19608177
Rorya:  If the word.doc is already open and you try and make another update it flashes a message that the doc is in use and is read only.  How can I make updates without have to close and save every time?  
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19613053
I think you would need to activate the document then reactivate the worksheet object.
I'm intrigued that you say the second code still pastes a picture, since the code doesn't paste anything at all!
Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19616189
I have multiple worksheet objects in one word document.  How do I get the code to decifer between the multiple worksheet objects?  For instance I may need range A10:D10 to be pasted into the first worksheet object and range A13:D17 pasted into the second worksheet object.  Also, I am having trouble making additional updates to the word doc if it is not saved and close first.  Is there any way to make updates regardless of whether the word doc is open or not?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19621787
The worksheets are stored as inline shapes and you should be able to refer to these by name.
I'm not sure about the other - I will have to investigate when I get a minute.
Regards,
Rory
0
 

Author Comment

by:tlbdst
ID: 19624288
How do you name or know what the name of these object are in order to refeer to them in the code?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question