jackadmin
asked on
Export CSV from Excel and exclude rows based on cell value
I have the following code in an Excel workbook that works perfectly to export values from a sheet into a CSV file. I now need to modify this.
All I need to do is to check every row in my range selection to see if the value in column I is equal to numeric 0. If I=0, then omit it from the CSV file and continue to the next row.
Can someone help me out with this?
All I need to do is to check every row in my range selection to see if the value in column I is equal to numeric 0. If I=0, then omit it from the CSV file and continue to the next row.
Can someone help me out with this?
Sub CreateAndExportCSVFile()
Dim fName As String
Dim fs
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim Sep As String
Dim Reply As Integer
Reply = MsgBox(Prompt:="Are you sure you are ready to create the payroll CSV file?", Buttons:=vbYesNo, Title:="Create Payroll CSV Confirmation")
If Reply = vbYes Then
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
Sep = ","
fName = ThisWorkbook.Path & "\" & ActiveSheet.Range("A1") & "_ultipro_wk_" & Format(Now, "dd-mmm-yyyy") & ".csv"
'Determine if the same CSV file aready exists 'If it does, DELETE it and create a new one from 'scratch Set fs = CreateObject("Scripting.FileSystemObject")
'If fs.FileExists(fname) Then
'Kill fname
'End If
If FileExists(fName) Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile (fName)
End If
Range("A1:I199").Select
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Open fName For Output Access Write As #FNum
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
Range("A1").Select
Else
Exit Sub
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER