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,Va­cationHours,VacationEndDate,BereavementHours,JuryDutyHours,ClericalHours,Wa­rehouseHours,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]

Check your other post.

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,Va­cationHours,VacationEndDate,BereavementHours,JuryDutyHours,ClericalHours,Wa­rehouseHours,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,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,

 

[Sandy]

Here is the reason for your extra commas when excel saved the worksheet
as a CSV file it saw some blank cells in your used range here's a quick
example:



In excel I typed this

A B C D E
1 1
2 1 2
3 1 2 3
4 1 2 3 4
5 1 2 3 4 5

then saved it as a CSV file
I opened the CSV file in wordpad (or notepad)
and this is what it shows


1,,,,
1,2,,,
1,2,3,,
1,2,3,4,
1,2,3,4,5

the commas are separating the blank values in the remaining cells in
the used range


Sandy