Opening csv in Word

[Willow]

Hi all

I have pasteSpecial / transposed a column into a row and saved as a cs
file. When this is opened in Word, it includes the empty column value
:-

test1;test2;test3;test4;test5;test6;test7;test8;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
and so on

The code I have used for the copy /paste special is:

' select and copy only the cells with numbers

ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Copy
MsgBox ("Range Selected and copied")

' paste transpose into the second sheet in the workbook

Worksheets("Sheet2").Activate
ActiveSheet.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
SkipBlanks:=True, Transpose:=True

MsgBox ("Transposed and pasted")

' Paste second sheet into new temporary workbook and save it as .cs
file and close new workbook

ActiveSheet.Copy


ActiveSheet.SaveAs Filename:="C:\Temp", FileFormat:=xlCSV

Workbooks("Temp").Activate
Worksheets("Temp").Activate
Workbooks("Temp").Close SaveChanges:=False

If this is done manually, the extra ::::: are not there.

Can anyone help - I am trying to get a CSV list onto the clipboard t
paste into another application....

Thanks


Li

 

[Dave Peterson]

Excel likes to put trailing delimiters based on the layout of the data. There's
a kb article that describes how this works, but I couldn't find it (and got
tired of looking!).

But maybe you could use a macro to write your text file yourself:

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may be happy with Earl's version--right out of the box!)