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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE