Print file in Excel VBA

Print file in Excel VBA
typescript
Ethan Jackson

I have the following code for generation of a text file on click of button in Excel which have three columns A (Account Number), B (Value C for credit and D for debit) and C (Amount). It is working fine and I have kept a condition that if the total credit amount and total debit amount is not equal then user gets the error on screen. User is getting the error as expected, however zero byte file is getting generated. I need that no file should get generated if total credit amount and total debit amount is not equal.

Option Explicit Dim rowcount As Long 'No. of records (rows) in the file (stores no_of_rows variable value) Dim counter As Long 'Counter variable Dim sim_line As String 'Text line to write to file & used as temporary variable Dim filenum As Integer 'The next file no. available on the machine to create a new file Dim filename As String Dim cr As Long Dim dr As Long Private Sub cmdok_Click() rowcount = Sheet1.no_of_rows If (Trim(txtfilename.Text) = "") Then MsgBox "Filename is mandatory", vbCritical GoTo Trap End If filename = Trim(txtfilename.Text) Application.ScreenUpdating = False If Not (Dir("C:\transfer\", vbDirectory) <> "") Then MkDir "C:\transfer" filenum = FreeFile Open "C:\transfer\" & filename For Output As #filenum Close #filenum filenum = FreeFile Open "C:\transfer\" & filename For Append As #filenum For counter = 2 To rowcount sim_line = "" sim_line = sim_line & Left(Trim(Range("A" & counter).Value) & Space(16), 16) sim_line = sim_line & "INR" sim_line = sim_line & Left(UCase(Trim(Range("B" & counter).Value)) & Space(1), 1) Range("C" & counter).NumberFormat = "0.00" sim_line = sim_line & Right(Space(17) & Trim(Range("C" & counter).Text), 17) If UCase(Trim(Range("B" & counter).Value)) = "C" Then cr = cr + Range("C" & counter).Value Else dr = dr + Range("C" & counter).Value End If Print #filenum, sim_line Next counter If (cr <> dr) Then MsgBox "Total Credit Amount is " & cr & " and Total Debit Amount is " & dr, vbCritical GoTo Trap End If Close #filenum MsgBox "TTUM Upload file generated successfully with file name " & filename & " in folder C:\transfer" Unload Me Trap: Application.ScreenUpdating = True End Sub

Answer

In VBA, when you use the statement: Open "C:\transfer\" & filename For Output As #filenumthe file is immediately created (or opened if it already exists), even if you never write to it. This behavior is inherent to how Open ... For Append works — the file is touched as soon as the Open statement runs.

You can check its size in your Trap-part and delete it if it’s empty:

Trap: Close #filenum ' Check file size and delete if empty filePath = "C:\transfer\" & filename If FileLen(filePath) = 0 Then Kill filePath End If

Further Reading

Related Articles