excel column to word with coma separated!!

[roshinpp_77]

Hi members,
This is my macro for copying a range named "rpp" to a new word doc generated in c:\autogenr.doc.



My input is:
col: C
1001
1002
1003
1004
1005
1006
1007

`my output in word has to be:
1001,1002,1003,1004,1005,1006,1007 etc..till the end


Below macro will copy the entire range as it is (as table) from excel to doc.

Sub cmdcopytoword_Click()
'copy sheet to clipboard
Range("rpp").Select
Selection.Copy
Range("B3").Select

' open Word
Dim appWD As Object
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

'open a new document in Word
appWD.Documents.Add DocumentType:=wdNewBlankDocument

' paste from clipboard
appWD.Selection.Paste
With appWD.ActiveDocument
.SaveAs "C:\autogenr.doc"
.Close
End With


But wha i require is not the entire range a table.but the contents of single column with coma separated in word.

Please provide some inputs.

Regsrds,
Roshin

[Charlie]

Maybe you can retrieve the values into an array and then join them into one
string before writing to the document

Dim vTmp() As Variant
Dim sTmp As String
vTmp = Application.WorksheetFunction.Transpose(Range("rpp").Value)
sTmp = Join(vTmp, ",")


"roshinpp_77" wrote:

>
> Hi members,
> This is my macro for copying a range named "rpp" to a new word doc
> generated in c:\autogenr.doc.
>
>
>
> My input is:
> col: C
> 1001
> 1002
> 1003
> 1004
> 1005
> 1006
> 1007
>
> `my output in word has to be:
> 1001,1002,1003,1004,1005,1006,1007 etc..till the end
>
>
> Below macro will copy the entire range as it is (as table) from excel
> to doc.
>
> Sub cmdcopytoword_Click()
> 'copy sheet to clipboard
> Range("rpp").Select
> Selection.Copy
> Range("B3").Select
>
> ' open Word
> Dim appWD As Object
> Set appWD = CreateObject("Word.Application")
> appWD.Visible = True
>
> 'open a new document in Word
> appWD.Documents.Add DocumentType:=wdNewBlankDocument
>
> ' paste from clipboard
> appWD.Selection.Paste
> With appWD.ActiveDocument
> .SaveAs "C:\autogenr.doc"
> .Close
> End With
>
>
> But wha i require is not the entire range a table.but the contents of
> single column with coma separated in word.
>
> Please provide some inputs.
>
> Regsrds,
> Roshin
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=562337
>
>