Macro Help?? Or maybe some other way to do t his
 

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

It is a simple change to remove 0's

savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
if ColCell < 0 then
ts.write ColCell.Value
end if
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

This will SOLVE your problem

Sub savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
ts.write ColCell.Value
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

All that macro does is save the file as a csv. It doesn't eliminate the 0's :(

"Joel" wrote:

This will SOLVE your problem

Sub savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
ts.write ColCell.Value
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

Joel,

I appreciate your help but this one is giving me compile errors near the
end. Again thank you for you help.

"Joel" wrote:

It is a simple change to remove 0's

savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
if ColCell < 0 then
ts.write ColCell.Value
end if
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

I just load the program from the posting and ran it with no errors. Are you
getting compiler errors or run errors?

Try on VBA menu debug - compile to determine if there is a compiler error.
Otherwise it is a run error. Run Errors may be cause by the data that is in
the cells.

I don't have clue why it shouldn't run. It ran before I put the check for
zero. I just tried breaking the code with different type data and was not
succesful. Need more info.

"MotoD" wrote:

Joel,

I appreciate your help but this one is giving me compile errors near the
end. Again thank you for you help.

"Joel" wrote:

It is a simple change to remove 0's

savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
if ColCell < 0 then
ts.write ColCell.Value
end if
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Macro Help?? Or maybe some other way to do t his
 

Oddly enough, I closed the document and re-opened it and tried
again..........and it ran fine. Now to right a batch program to make this all
happen without anyone messing it up :)

Thanks

"Joel" wrote:

I just load the program from the posting and ran it with no errors. Are you
getting compiler errors or run errors?

Try on VBA menu debug - compile to determine if there is a compiler error.
Otherwise it is a run error. Run Errors may be cause by the data that is in
the cells.

I don't have clue why it shouldn't run. It ran before I put the check for
zero. I just tried breaking the code with different type data and was not
succesful. Need more info.

"MotoD" wrote:

Joel,

I appreciate your help but this one is giving me compile errors near the
end. Again thank you for you help.

"Joel" wrote:

It is a simple change to remove 0's

savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
if ColCell < 0 then
ts.write ColCell.Value
end if
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?