Peskarik
11-04-2010, 01:50 AM
Hello, everyone
I have Excel 2003 SP2.
I have a sheet that contains different data in different columns
On another sheet I create comma-separated data in column 1 with VBA code that looks something like this:
Set wsJP = wb.Sheets(4)
wsJP.Columns(1).NumberFormat = "General"
wsJP.Cells(itJP, 1) = jpName & "," & strDate & "," & "S" & "," & _
wsData.Cells(i, lastCol + 3) & "," & wsData.Cells(i,lastCol + 2) & _
"," & wsData.Cells(i, colCURR) & "," & ", , , , , , , , , ,"
Then I copy the entire sheet 4 to another file with following code:
Application.DisplayAlerts = False
Sheets(4).Copy
wsName = "AtlasUpload_djim_europe_" & strDate
ActiveWorkbook.SaveAs Filename:=OutputPath & wsName & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Save
ActiveWindow.Close
Later, for checking purposes, when I try to import the data from the newly created file in Excel with:
Data/Import External Data/Import Data
...Delimited file
I see in the preview window that I have double quotes around the data, something like:
"bmdjimeu,20101029,S,0028262,337999,GBP,,,,,,,,,,,"
and then when I set comma as a delimiter nothing happens, because of these doublequotes.
On the other hand, if, instead of copying the sheet 4 through VBA code above, I copy it by hand and then save the file by hand in CSV format, I have a pop-up window telling me something like:
Book4.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the book in this format?
To keep this format, which leaves out any incompatible features, click YES.
When I click YES, the file is saved, and later, when I import data from that file in Excel, I see:
bmdjimeu,20101029,S,0028262,337999,GBP,,,,,,,,,,,
so, no double quotes, and import as comma-separated file works.
How can I solve the double-quote problem in VBA?
Please help!
Thank you in advance.
Regards,
Sergey
I have Excel 2003 SP2.
I have a sheet that contains different data in different columns
On another sheet I create comma-separated data in column 1 with VBA code that looks something like this:
Set wsJP = wb.Sheets(4)
wsJP.Columns(1).NumberFormat = "General"
wsJP.Cells(itJP, 1) = jpName & "," & strDate & "," & "S" & "," & _
wsData.Cells(i, lastCol + 3) & "," & wsData.Cells(i,lastCol + 2) & _
"," & wsData.Cells(i, colCURR) & "," & ", , , , , , , , , ,"
Then I copy the entire sheet 4 to another file with following code:
Application.DisplayAlerts = False
Sheets(4).Copy
wsName = "AtlasUpload_djim_europe_" & strDate
ActiveWorkbook.SaveAs Filename:=OutputPath & wsName & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Save
ActiveWindow.Close
Later, for checking purposes, when I try to import the data from the newly created file in Excel with:
Data/Import External Data/Import Data
...Delimited file
I see in the preview window that I have double quotes around the data, something like:
"bmdjimeu,20101029,S,0028262,337999,GBP,,,,,,,,,,,"
and then when I set comma as a delimiter nothing happens, because of these doublequotes.
On the other hand, if, instead of copying the sheet 4 through VBA code above, I copy it by hand and then save the file by hand in CSV format, I have a pop-up window telling me something like:
Book4.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the book in this format?
To keep this format, which leaves out any incompatible features, click YES.
When I click YES, the file is saved, and later, when I import data from that file in Excel, I see:
bmdjimeu,20101029,S,0028262,337999,GBP,,,,,,,,,,,
so, no double quotes, and import as comma-separated file works.
How can I solve the double-quote problem in VBA?
Please help!
Thank you in advance.
Regards,
Sergey