Sheet name changes when saved in CSV format (XL2000)

Topic

New Reply

I haven’t worked much with the CSV format and have had no success in finding an answer or a work around to this. I am building a worksheet that has data input from a user form and then is saved as a CSV file. When the sheet is saved, the sheet name changes to file name. If I change the sheet name back and close the file and I answer yes to saving changes …

…the sheet name is changed back to the file name.

This is not too big of a deal except for the fact that it is bugging the stuffing out of me. Is this normal behavior for the CSV file format?

Reply | Quote

Replies

A CSV file is just a text file, not a spreadsheet. If you open a CSV file in NotePad, you will see that it’s just data, separated by the Windows List Separator character – in many countries the comma acts as separator, hence the abbreviation CVS = Comma Separated Values.

A CSV file stores no information at all on formatting, formulas, sheets etc. – just values.

Reply | Quote

Thanks Hans for the reply. Guess this is something that I will have to live with.

Reply | Quote

Just a further note to Hans explanation; by virtue of it’s structure a CSV file is a single sheet of data which Excel recognizes; one cannot have a multiple sheet CSV.

Reply | Quote

Thanks for the further information. Aesthetically it was bothering me that the sheet name shows AL 1 before being saved and Availability Notice_AL 1_ for 01 after having been saved in the CSV format. The file name is: Availability Notice_AL1_ for 010102.csv , apparently the sheet tab will read the same as the file name in a CSV format. I have read that the sheet name can be 31 characters long and as such the reason why the tab shows what it does.

Reply | Quote

It is “by design” but can easily be avoided:

Lets say your csv sheet is (previously) named “ExportSheet”:

Sub myExport()
Dim oExportsheet as Worksheet
Dim oCSVBook as Workbook
set oExportsheet=Thisworkbook.Worksheets(“ExportSheet”)
‘create a copy of the exportsheet in a new workbook
Set oCSVBook=oExportSheet.Copy
‘Save new wrokbook as CSV
oCSVBook.SaveAs FileName:=”C:DataCSVFilename.CSV”,Fileformat:=xlCSV
‘ Close CSV
oCSVBook.Close False
End Sub

Reply | Quote

Jan,

I have an issue with the following line of code and receive an error message on the copy portion.

Set oCSVBook = oExportsheet.Copy

Thanks,
John

Reply | Quote

The Copy method does not return a result. Use this instead:

oExportSheet.Copy
Set oCSVBook = ActiveWorkbook

Reply | Quote

Which proves I didn’t test my solution.

Thanks.

Reply | Quote

Which proves I didn’t test my solution.

Thanks.

Reply | Quote

The Copy method does not return a result. Use this instead:

oExportSheet.Copy
Set oCSVBook = ActiveWorkbook

Reply | Quote

Jan,

I have an issue with the following line of code and receive an error message on the copy portion.

Set oCSVBook = oExportsheet.Copy

Thanks,
John

Reply | Quote