 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / March 2006Copy from Excel to Word in VB
Is it possible to copy documents stored in Excel (which include text and graphs) and paste them in Word using Visual Basic? I realize you can complete this task manually but the process I am building requires automation. I am not sure how to switch the Visual Basic from the copy command in Excel to the paste command in word. Are you talking about "Visual Basic" as in VB Classic (6.0) or VB.Net? Or are you talking about the Visual Basic for Applications macro language in either Excel or Word? Ed > Is it possible to copy documents stored in Excel (which include text and > graphs) and paste them in Word using Visual Basic? I realize you can > complete this task manually but the process I am building requires > automation. I am not sure how to switch the Visual Basic from the copy > command in Excel to the paste command in word. Ed, Most first option would be Visual Basic for Application in either Excel or Word but I also have access to Visual Basic Classic (6.0) if this is my only option. Specifically, what I can not figure out is if I am in Word, what is the call in Visual Basic for Applications to open an Excel spreadsheet in Excel and them to reference cells in that spreadsheet. > Are you talking about "Visual Basic" as in VB Classic (6.0) or VB.Net? Or > are you talking about the Visual Basic for Applications macro language in [quoted text clipped - 7 lines] > > automation. I am not sure how to switch the Visual Basic from the copy > > command in Excel to the paste command in word. Tony's got it for you in his reply. I use this approach quite often, as well as from Excel into Word. The only time I had to put anything into VB6 was when I had version and macro security conflicts across a network; it was then easier to put everything into a VB app that didn't raise either issue. Ed > Ed, > [quoted text clipped - 19 lines] >> > automation. I am not sure how to switch the Visual Basic from the copy >> > command in Excel to the paste command in word. If you are starting from Word ... Set a reference (Tools > References from the VBE menu) to "Microsoft Excel x.x Library" Then code something along these lines ... Dim xlApp as Excel.Application Dim xlBook as Excel.Workbook Set xlApp = CreateObject("Excel.Appliation") Set xlBook = xlApp.Workbooks.Open("path\and\name\of\your\workbook.xls") xlBook.Sheets(1).Range("A1:B10").Copy Selection.Paste xlBook.Close xlApp.Quit Set xlBook = Nothing Set xlApp = Nothing Obviously there is a lot more to it - selecting objects to copy differs from cell ranges, etc. but that structure will give you access to an Excel Workbook and copy whatever to the Selection (explicit Ranges would be better - but this is just a sample) and close Excel at the end. -- Enjoy, Tony > Is it possible to copy documents stored in Excel (which include text and > graphs) and paste them in Word using Visual Basic? I realize you can > complete this task manually but the process I am building requires > automation. I am not sure how to switch the Visual Basic from the copy > command in Excel to the paste command in word. Hi Tony, What do you do if you are starting from Excell? I want to create and manipulate word tables from Excell vb code. Coud you possibly send just a few lines of code showing how one can do this, to start me off. And, where can I find a free help or documentation on the subject. Thanks in advance  SignatureAyse "Tony Jollans": > If you are starting from Word ... > [quoted text clipped - 32 lines] > > automation. I am not sure how to switch the Visual Basic from the copy > > command in Excel to the paste command in word. Essentially the same process .. Set a reference (Tools > References from the VBE menu) to "Microsoft Word x.x Library" Then code something along these lines ... Dim wdApp as Word.Application Dim wdDoc as Word.Document Set wdApp = CreateObject("Word.Appliation") Set wdDoc = wdApp.Documents.Open("path\and\name\of\your\document.doc") Sheets(1).Range("A1:B10").Copy wdApp.Selection.Paste wdDoc.Save wdDoc.Close wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing As before there's more to it depending on what you actually want to do - and as before, Ranges are better than the Selection, but I hope that gets you started. -- Enjoy, Tony > Hi Tony, > [quoted text clipped - 45 lines] > > > automation. I am not sure how to switch the Visual Basic from the copy > > > command in Excel to the paste command in word. With all respect to Tony, who has bailed me out a time or three, I added a bit to his code (and fixed a typo in the CreateObject line). If you move the Selection back after pasting, you are in the table you have just created in Word. Then you can set an object to the table and manipulate as you wish using Word's Table object properties and methods. Ed Sub WordTableTest() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTab As Word.Table Set wdApp = CreateObject("Word.Application") Set wdDoc = wdApp.Documents.Open("path\and\name\of\your\document.doc") Sheets(1).Range("A1:B10").Copy wdApp.Visible = True wdApp.Selection.Paste wdApp.Selection.Move Unit:=wdCharacter, Count:=-2 Set wdTab = wdApp.Selection.Tables(1) wdTab.Shading.ForegroundPatternColor = wdColorBrightGreen wdDoc.Save wdDoc.Close wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub > Essentially the same process .. > [quoted text clipped - 85 lines] > copy >> > > command in Excel to the paste command in word. Thanks very much, thats very helpfull as a start. I'm afraid I have never programed for word objects. Can you point me to some free reference concerning Word's object properties and methods. Thank you SignatureAyse "Ed": > With all respect to Tony, who has bailed me out a time or three, I added a > bit to his code (and fixed a typo in the CreateObject line). If you move [quoted text clipped - 118 lines] > > copy > >> > > command in Excel to the paste command in word. Help - or the Object Browser in Word (or the object browser in Excel if you have a reference to Word) - is as good a place as any to start. -- Enjoy, Tony > Thanks very much, thats very helpfull as a start. > [quoted text clipped - 130 lines] > > > copy > > >> > > command in Excel to the paste command in word. You can place your cursor on a term in your macro and press F1 - it will bring up a Help topic for that term. Look at the top for Properties, Methods, and/or Applies To. Make sure as Tony said that you have a reference to Word (Tools >> References >> Microsoft Word object). Ed > Help - or the Object Browser in Word (or the object browser in Excel if > you [quoted text clipped - 160 lines] >> > > copy >> > >> > > command in Excel to the paste command in word. Thanks Ed, Thanks Tony, cheers SignatureAyse "Ed": > You can place your cursor on a term in your macro and press F1 - it will > bring up a Help topic for that term. Look at the top for Properties, [quoted text clipped - 167 lines] > >> > > copy > >> > >> > > command in Excel to the paste command in word. Hi, Does one compile vb code writen in excell vb and make an executable file like normal vb? In excell, one of the optios is "compile on demand", but i cant find the place to compile from. And writing "compile" in excell help produces nothing. SignatureAyse "Ed": > You can place your cursor on a term in your macro and press F1 - it will > bring up a Help topic for that term. Look at the top for Properties, [quoted text clipped - 167 lines] > >> > > copy > >> > >> > > command in Excel to the paste command in word. VBA code does not compile to a separate executable. It is not essential - but it is a good idea - to compile before saving the containing Workbook (or Document or whatever) for at least two reasons --- (a) to check for any errors (b) for performance - to stop it being compiled every time it's run If compiled, the compiled version of the code is stored alongside the source in the Workbook. If you don't compile, the source will be compiled to somewhere temporary every time the code is run. -- Enjoy, Tony > Hi, > [quoted text clipped - 179 lines] > > >> > > copy > > >> > >> > > command in Excel to the paste command in word. Thanks Tony, cheers SignatureAyse "Tony Jollans": > VBA code does not compile to a separate executable. > [quoted text clipped - 216 lines] > > > >> > > copy > > > >> > >> > > command in Excel to the paste command in word. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.