Creating CSV Comma Delimited File

[Connie]

I am using the following code to create a CSV file for a sheet within a
workbook. I have posted the CSV file that gets created and there are 2
problems with the file. The first is that there is an extra comma
placed at the end of each row. In some cases the comma is there, and
in other cases it is not there (although for the sample I've provided
the extra column is there for each row). Secondly, there is a group of
commas at the end of the file. I think this situation can be
eliminated if I specify the actual range for which I want to create the
CSV file. Does anyone know the syntax for that?

Thanks. Connie

' Create CSV file for Technicians
Sheets("Field Rep Time Sheet").Select
Directory = CurDir
FName = "\Upload Tech " & Format(Now(), "yyyymmmddhhmm")
FileName = Trim(Directory) + Trim(FName)
Sheets("Upload Data Tech").Copy
Set wb = ActiveWorkbook
wb.SaveAs FileName & ".csv", FileFormat:=xlCSV
wb.Close SaveChanges:=False
MsgBox "Upload File Saved for TECHNICIANS"




Here is the data in the worksheet. The last column is column O and is
either 20 or 0.

EndDate EmployeeName OracleID TechNo TotalHours TrainingHours TechOffice VacationHours VacationEndDate BereavementHours JuryDutyHours ClericalHours WarehouseHours TotalHours PerDiem
10/7/2006 Michael
Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/12/2006 Michael
Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Michael
Albert 23709 1234 0 1.916666667 4.1 0 5.466666667 0 0 0 11.48333333 0
10/7/2006 Michael Albert 23709 1234 7 4 0 0 0 0 0 0 11 20
10/8/2006 Michael Albert 23709 1234 5.85 0 4.1 0 0 0 0 0 9.95 0





Here is the data in the CSV file. Notice that there is an extra comma
at the end of each row, and there are commas at the end of the file.
The commas at the end of the file actually extend to I believe 65000
rows.

EndDate,EmployeeName,OracleID,TechNo,TotalHours,TrainingHours,TechOffice,VacationHours,VacationEndDate,BereavementHours,JuryDutyHours,ClericalHours,WarehouseHours,TotalHours,PerDiem,,
10/7/2006,Michael
Albert,23709,1234,7.00,4.00,0.00,0.00,,0.00,0.00,0.00,0.00,11.00,20,,
10/12/2006,Michael
Albert,23709,1234,1.92,0.00,4.10,5.70,10/12/2006,0.00,0.00,0.00,0.00,11.72,0,,
10/13/2006,Michael
Albert,23709,1234,0,1.916666667,4.1,0,,5.466666667,0,0,0,11.48333333,0,,
10/7/2006,Michael Albert,23709,1234,7,4,0,0,,0,0,0,0,11,20,,
10/8/2006,Michael Albert,23709,1234,5.85,0,4.1,0,,0,0,0,0,9.95,0,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,

 

[Dave Peterson]

You may be able to eliminate the lines with just commas on it by resetting the
used range.

Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Or you could copy just the data to a worksheet in a new workbook and save that
new workbook as a .csv file.

But I think that the commas at the end of the line are beyond your control if
you use .saveas.

Saved from a previous post:

This might describe the problem of too many commas in CSV files:

http://support.microsoft.com/default.aspx?scid=77295
Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But a lot of programs (excel included) don't care about those extra columns.
Maybe you don't have to care, either???)

Maybe you could write your own exporting program that would behave exactly the
way you want:

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

(or maybe you could build your own formula and copy|paste into Notepad.)

==============
Take a look at Earl's program. I bet it does what you want.