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 <4e80ac95$0$22714$c3e8da3$(E-Mail Removed)> of Mon,
26 Sep 2011 18:47:12 in microsoft.public.excel.programming, KP
<none@?.?.invalid> 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" <(E-Mail Removed)> skrev i en meddelelse
news:v3$(E-Mail Removed)...
> In message <4e80ac95$0$22714$c3e8da3$(E-Mail Removed)> of Mon,
> 26 Sep 2011 18:47:12 in microsoft.public.excel.programming, KP
> <none@?.?.invalid> 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