Copy from Excel to Word in VB

[j schmidt]

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]

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

 

[j schmidt]

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.

 

[Tony Jollans]

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.

 

[Ed]

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

 

[Ayse]

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

 

[Tony Jollans]

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.

 

[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
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

 

[Ayse]

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

 

[Tony Jollans]

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.

 

[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,
Methods, and/or Applies To. Make sure as Tony said that you have a
reference to Word (Tools >> References >> Microsoft Word object).

Ed

 

[Ayse]

Thanks Ed, Thanks Tony,

cheers
--
Ayse


"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,
Methods, and/or Applies To. Make sure as Tony said that you have a
reference to Word (Tools >> References >> Microsoft Word object).

Ed

 

[Ayse]

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.

 

[Tony Jollans]

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.

 

[Ayse]

Thanks Tony,

cheers
--
Ayse


"Tony Jollans":

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.