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
Connie wrote:
> 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,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
> ,,,,,,,,,,,,,,,,
Similar Threads
