CSV export help

[mainemike]

I have a General format column. One of the cells is 359 characters in
length. When I export the column using..

ActiveWorkbook.SaveAs Filename:=NFName, FileFormat:=xlCSV,
CreateBackup:=False

and then when I open the .csv file, the cell is truncated to 255. Even
changing the format to Text, I still only have 255 characters.

I did try to import the data directly from the workbook into Access,
but I ran into the 255 character problem too. I was told that
exporting the worksheets to CSV prior to importing them into Access
would allow me to get the full 359 vs 255, but that's proving not to be
correct. The problem seems to be pointing to Excel and not Access as
the limiting factor.

Is there some other file or cell format that would allow me to export
the entire contents? I need to eventually import the data into Access,
and I do have headers. CSV is one of the easier formats I've found to
do this with.

 

[Dave Peterson]

I just tried this with a few cells filled with 500 character strings.

I saved it as a .csv file and opened that .csv file in a text editor. The
string lengths were what they were supposed to be (500 characters each).

I reopened the .csv file in excel and those cells still contained strings of 500
characters.


Ahhhhhhh.

Part of the code that you didn't share was a command that copied the worksheet
to a new workbook--so you could save from there, right?

If that's true, then that's what caused the trouble.

If you did that same copying of a worksheet (with strings > 255), you'd get a
warning message. Using code won't cause the warning.

The easiest way to overcome this problem is to copy the sheet, copy and paste
the cells, then do the file|saveAs.

Dim wksToCopy As Worksheet
Dim NewWks As Worksheet

Set wksToCopy = Worksheets("sheet1")

wksToCopy.Copy
Set NewWks = ActiveSheet

wksToCopy.Cells.Copy _
Destination:=NewWks.Range("a1")

With NewWks
.Parent.saveas filename:=..., fileformat:=xlcsv, ...
.Close savechanges:=False
End With