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, ",")