Copy from Excel to Word

[KP]

Hi,

Can someone help with a macro doing the following:

1. Copy a range from Excel sheet1 (e.g. A1:F30)
2. Open Word - new document
3. Paste the copied range to Word document
4. Close excel workbook without save
5. Save as dialogue box for created Word document

Regards,
Kaj Pedersen

 

[Walter Briscoe]

In message <[email protected]> of Mon,
26 Sep 2011 18:47:12 in microsoft.public.excel.programming, KP

Hi,

Can someone help with a macro doing the following:

1. Copy a range from Excel sheet1 (e.g. A1:F30)
2. Open Word - new document
3. Paste the copied range to Word document
4. Close excel workbook without save
5. Save as dialogue box for created Word document

What have you tried?

1) I would try recording a macro, in word, which does what you want in
word.
2) Then record a macro to copy a given range to the clipboard.

3) You will need some code to start Word from an Excel macro.
Something like:

Dim Docs As Word.Application

Set Docs = CreateObject("Word.Application"): Docs.Visible = True
With Docs
.Documents.Open Filename:=foo
.Documents.Item(1).SaveAs Filename:=bar
End With

N.B. Dim Docs As Word.Application uses early binding.
If it fails for you, try Dim Docs as Object.

You probably want a method other than Open for your file.
Use Help in the Word VBE (Visual Basic Editor) to see what is available.

You then glue 1, 2, and 3 together and should be in business.

 

[KP]

Thank you very much.

This hint from you helped me a lot:

"N.B. Dim Docs As Word.Application uses early binding.
If it fails for you, try Dim Docs as Object."

Now I think I will succeed.

Regards
Kaj Pedersen