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