QuickBooks IIF File Format Code
Author: James W. Hall Sr.
Rating:
Rate this Resource
Visits: 19992
Discuss in Newsgroups
No matter where I looked for a Quickbooks IIF code example, I could not find one. I completed this code and tested it. The following code writes out to a bill.iif and invoice.iif file. The variables are queried from an Access database. The import works! Have fun importing.
Public Sub qbImport()
Dim fname As String
Dim qbtranheader As String
Dim qbsplheader As String
Dim qbendtranhdr As String
Dim qbtran As String
Dim qbspl As String
Dim qbendtran As String
fname = "c:\bill.iif"
Dim FSO As New FileSystemObject
Dim FSTR As Scripting.TextStream
On Error Resume Next
Set FSTR = FSO.OpenTextFile(fname, ForWriting, Not FSO.FileExists(fname))
'Create bill header
qbtranheader = "!TRNS" & vbTab & "TRNSID" & vbTab & "TRNSTYPE" & vbTab & "DATE" & vbTab
qbtranheader = qbtranheader & "ACCNT" & vbTab & "NAME" & vbTab & "CLASS" & vbTab & "AMOUNT" & vbTab
qbtranheader = qbtranheader & "DOCNUM" & vbTab & "MEMO" & vbTab & "CLEAR" & vbTab & "TOPRINT" & vbTab
qbtranheader = qbtranheader & "ADDR5" & vbTab & "DUEDATE" & vbTab & "TERMS"
qbsplheader = "!SPL" & vbTab & "SPLID" & vbTab & "TRNSTYPE" & vbTab & "DATE" & vbTab
qbsplheader = qbsplheader & "ACCNT" & vbTab & "NAME" & vbTab & "CLASS" & vbTab & "AMOUNT" & vbTab
qbsplheader = qbsplheader & "DOCNUM" & vbTab & "MEMO" & vbTab & "CLEAR" & vbTab & "QNTY" & vbTab
qbsplheader = qbsplheader & "REIMBEXP" & vbTab & "SERVICEDATE" & vbTab & "OTHER2"
qbendtranhdr = "!ENDTRNS"
qbendtran = "ENDTRNS"
FSTR.Write qbtranheader & vbNewLine
FSTR.Write qbsplheader & vbNewLine
FSTR.Write qbendtranhdr & vbNewLine
'end create bill header line
'Writing the invoice record to invoice.iif
Dim fname1 As String
Dim qbtranheader1 As String
Dim qbsplheader1 As String
Dim qbendtranhdr1 As String
Dim qbtran1 As String
Dim qbspl1 As String
Dim qbsp2 As String
Dim qbendtran1 As String
fname1 = "c:\invoice.iif"
Dim FSO1 As New FileSystemObject
Dim FSTR1 As Scripting.TextStream
On Error Resume Next
Set FSTR1 = FSO1.OpenTextFile(fname1, ForWriting, Not FSO1.FileExists(fname1))
'Create invoice header
qbtranheader1 = "!TRNS" & vbTab & "TRNSID" & vbTab & "TRNSTYPE" & vbTab & "DATE" & vbTab
qbtranheader1 = qbtranheader1 & "ACCNT" & vbTab & "NAME" & vbTab & "CLASS" & vbTab & "AMOUNT" & vbTab
qbtranheader1 = qbtranheader1 & "DOCNUM" & vbTab & "MEMO" & vbTab & "CLEAR" & vbTab & "TOPRINT" & vbTab
qbtranheader1 = qbtranheader1 & "NAMEISTAXABLE" & vbTab & "ADDR1" & vbTab & "ADDR3" & vbTab & "TERMS"
qbsplheader1 = "!SPL" & vbTab & "SPLID" & vbTab & "TRNSTYPE" & vbTab & "DATE" & vbTab
qbsplheader1 = qbsplheader1 & "ACCNT" & vbTab & "NAME" & vbTab & "CLASS" & vbTab & "AMOUNT" & vbTab
qbsplheader1 = qbsplheader1 & "DOCNUM" & vbTab & "MEMO" & vbTab & "CLEAR" & vbTab & "QNTY" & vbTab
qbsplheader1 = qbsplheader1 & "PRICE" & vbTab & "INVITEM" & vbTab & "TAXABLE" & vbTab & "EXTRA"
qbendtranhdr1 = "!ENDTRNS"
qbendtran1 = "ENDTRNS"
FSTR1.Write qbtranheader1 & vbNewLine
FSTR1.Write qbsplheader1 & vbNewLine
FSTR1.Write qbendtranhdr1 & vbNewLine
'end create invoice header line
'Get the billed records for processing
Dim tsearch As String
tsearch = "select * from trip_order where status = 'BILLED'"
tsearch = tsearch & " order by pu_date, trip_id"
Dim Cn As New Connection, Rs77 As New Recordset
Cn.ConnectionString = "DSN=Dimension"
Cn.Open
Dim carrier As String, tripid As String, carriertotal As String, relnum As String
Dim customer As String, customertotal As String, commodity As String
Dim freightcost As String, fuelsurcharge As String
Rs77.Source = tsearch
Set Rs77.ActiveConnection = Cn
Rs77.Open
Do While Rs77.EOF = False
If IsNull(Rs77!carrier) Then
carrier = " "
Else
carrier = Rs77!carrier
End If
If IsNull(Rs77!trip_id) Then
tripid = " "
Else
tripid = Rs77!trip_id
End If
If IsNull(Rs77!carrier_total) Then
carriertotal = " "
Else
carriertotal = Rs77!carrier_total
End If
If IsNull(Rs77!rel_num) Then
relnum = " "
Else
relnum = Rs77!rel_num
End If
If IsNull(Rs77!customer) Then
customer = " "
Else
customer = Rs77!customer
End If
If IsNull(Rs77!total_cost) Then
customertotal = " "
Else
customertotal = Rs77!total_cost
End If
If IsNull(Rs77!commodity) Then
commodity = " "
Else
commodity = Rs77!commodity
End If
If IsNull(Rs77!freight_cost) Then
freightcost = " "
Else
freightcost = Rs77!freight_cost
End If
If IsNull(Rs77!fuel_surcharge) Then
fuelsurcharge = " "
Else
fuelsurcharge = Rs77!fuel_surcharge
End If
Dim tdate As String, memo As String, duedate As Date, duedated As String
memo = "PRO# " + tripid
tdate = Format(Now(), "mm/dd/yyyy")
duedated = Format((Now() + 30), "mm/dd/yyyy")
carriertotal = Format(carriertotal, "###.##")
Dim carriertotaln As Integer
Dim carriertotald As Integer
carriertotaln = (carriertotal * -1)
carriertotald = (carriertotal * 1)
customertotal = Format(customertotal, "###.##")
Dim customertotaln As Integer
Dim customertotald As Integer
customertotaln = (customertotal * -1)
customertotald = (customertotal * 1)
'create and write bill transaction lines
qbtran = "TRNS" & vbTab & " " & vbTab & "BILL" & vbTab & tdate & vbTab & "Accounts Payable" & vbTab
qbtran = qbtran & carrier & vbTab & " " & vbTab & carriertotaln & vbTab & relnum & vbTab & memo & vbTab
qbtran = qbtran & "N" & vbTab & "N" & vbTab & " " & vbTab & duedated & vbTab & "Net 30"
FSTR.Write qbtran & vbNewLine
qbspl = "SPL" & vbTab & " " & vbTab & "BILL" & vbTab & tdate & vbTab & "Carrier:Fees" & vbTab & " " & vbTab
qbspl = qbspl & " " & vbTab & carriertotald & vbTab & " " & vbTab & " " & vbTab & "N" & vbTab & " " & vbTab & "NOTHING" & vbTab & "0/0/0"
FSTR.Write qbspl & vbNewLine
FSTR.Write qbendtran & vbNewLine
qbtran = ""
qbspl = ""
'CREATE INVOICE TRANSACTION LINE
qbtran = "TRNS" & vbTab & " " & vbTab & "INVOICE" & vbTab & tdate & vbTab & "Accounts Receivable" & vbTab
qbtran = qbtran & customer & vbTab & " " & vbTab & customertotald & vbTab & tripid & vbTab & " " & vbTab
qbtran = qbtran & "N" & vbTab & "N" & vbTab & "N"
FSTR1.Write qbtran & vbNewLine
qbspl = "SPL" & vbTab & " " & vbTab & "INVOICE" & vbTab & tdate & vbTab & "Sales" & vbTab & " " & vbTab
qbspl = qbspl & " " & vbTab & customertotaln & vbTab & " " & vbTab & commodity & vbTab & "N" & vbTab & " " & vbTab & customertotald & vbTab
qbspl = qbspl & " " & vbTab & "N"
FSTR1.Write qbspl & vbNewLine
qbsp2 = "SPL" & vbTab & " " & vbTab & "INVOICE" & vbTab & tdate & vbTab & "Sales Tax Payable" & vbTab & " " & vbTab
qbsp2 = qbsp2 & " " & vbTab & "0" & vbTab & " " & vbTab & " " & vbTab & "N" & vbTab & " " & vbTab & "0.00%" & vbTab & "No Tax" & vbTab
qbsp2 = qbsp2 & "N" & vbTab & "AUTOSTAX"
FSTR1.Write qbsp2 & vbNewLine
FSTR1.Write qbendtran & vbNewLine
qbtran = ""
qbspl = ""
qbsp2 = ""
Rs77.MoveNext
Loop
Rs77.Close
Cn.Close
Set FSTR = Nothing
Set FSO = Nothing
'Update the status on all records that are being imported into Quickbooks.
Dim bstatus As String, cstatus As String, btripid As String
'cstatus = "COMPLETED"
'bstatus = "BILLED"
'Dim sqlupdate As ADODB.Command
'Set sqlupdate = New ADODB.Command
'sqlupdate.ActiveConnection = "DSN=Dimension"
'sqlupdate.CommandText = "update trip_order " & _
' "set status = '" & cstatus & "' " & _
' " where status = '" & bstatus & "'"
'sqlupdate.Execute
'Set sqlupdate = Nothing
MsgBox "Imports have been created: Please import the files bill.iif and invoice.iif from Quickbooks", vbOKOnly, "QuickBooks Import Completed"
end sub
Visit my guru profile
Visitor Comments
Be the first to rate this code sample!
