Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |