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?
|