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

In message om of Mon,
26 Sep 2011 18:47:12 in microsoft.public.excel.programming, KP
writes
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.
--
Walter Briscoe

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


"Walter Briscoe" skrev i en meddelelse
...
In message om of Mon,
26 Sep 2011 18:47:12 in microsoft.public.excel.programming, KP
writes
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.
--
Walter Briscoe