Write Nulls to Text file 1

[pluto1415]

I've got an excel spreadsheet that will contain data in up to 3 columns. If column A is populated, then either column B or column C (or both) will also be populated. If A is empty then B&C are by definition empty (even if someone makes a mistake and enters stuff there) A is a date, B&C are currency.

My VBA reads these rows and writes to a pipe delimited text file for import into another system we have. The problem I'm having is that if either B or C is null (which it frequently is) then the code just bombs out and says Type Mismatch. What I need it to do is just write a zero.

Sub ExportPremiums_Click()
    Dim LastDataRow As Integer
    Dim StartDataRow As Integer
    Dim OutPutFileName As String
    Dim OutPath As String
    Dim FileName As String
    Dim NumRows As Integer
    Dim PremAmt As Currency
    Dim PremDate As Date
    Dim LoanAmt As Currency
    
    'Check for Policy Number
    If [b1] <> "" Then
       'Unlock sheet
            Worksheets("sheet1").Unprotect Password:="password"
        'Obtain Number of the Last Row that has Premium Data
        StartDataRow = 4  'This is always the first row that has Premium Data
        With [A4].CurrentRegion
            LastDataRow = .Rows.Count + .Row - 1
        End With
        
        'Declare name and location for new .txt file
        'Use the policy number found on another tab to name the file
        FileNum = FreeFile()
        FileName = Worksheets("Sheet1").Cells(1, 2).Value
        OutPath = "C:\Premium\"
        OutPutFileName = OutPath & FileName & ".txt"
        Open OutPutFileName For Output As #FileNum
        
        'Determine total number of rows to write out
        NumRows = LastDataRow - StartDataRow + 1
    
        'Write Rows
        For x = 1 To NumRows
            PremDate = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 1).Value)
            PremAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 2).Value)
            LoanAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 3).Value)
            
            Print #FileNum, PremDate & "|" & PremAmt & "|" & LoanAmt
          
        Next x
        Close #FileNum
        
       'Re-Lock sheet
        Worksheets("Sheet1").Protect Password:="password"
        MsgBox ("Output file has been created, go onto the next step")
    
    Else: MsgBox ("No Policy Number Entered")
    End If
    
End Sub

 

[SkipVought]

hi,

"Type Mismatch" on what statement?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pluto1415]

Sorry - it's on whichever line is currently null - so one of these:
PremAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 2).Value)
LoanAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 3).Value)

 

[SkipVought]

It is the Data Type that you declared for these variables.

Your NUMERIC variables cannot be NULL. They can only be ZERO or any other real number.

You might consider using the Variant data type.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]