software

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.
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 gt; 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(quot;sheet1quot;)

wksToCopy.Copy
Set NewWks = ActiveSheet

wksToCopy.Cells.Copy _
Destination:=NewWks.Range(quot;a1quot;)

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

mainemike wrote:
gt;
gt; I have a General format column. One of the cells is 359 characters in
gt; length. When I export the column using..
gt;
gt; ActiveWorkbook.SaveAs Filename:=NFName, FileFormat:=xlCSV,
gt; CreateBackup:=False
gt;
gt; and then when I open the .csv file, the cell is truncated to 255. Even
gt; changing the format to Text, I still only have 255 characters.
gt;
gt; I did try to import the data directly from the workbook into Access,
gt; but I ran into the 255 character problem too. I was told that
gt; exporting the worksheets to CSV prior to importing them into Access
gt; would allow me to get the full 359 vs 255, but that's proving not to be
gt; correct. The problem seems to be pointing to Excel and not Access as
gt; the limiting factor.
gt;
gt; Is there some other file or cell format that would allow me to export
gt; the entire contents? I need to eventually import the data into Access,
gt; and I do have headers. CSV is one of the easier formats I've found to
gt; do this with.
gt;
gt; --
gt; mainemike

--

Dave Peterson