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
