 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: before December, 1998
This is an example in which data is exported in the Quickbooks export format. The print # statement lays dowm exactly what you want.
Sub QBexport '-----QuickBooks(tm) Export Script '-----Jerry Sikes 2.7.98 '----- '-----Declare Account Constants Const A1$ = "A/R 902:Direct - 902" Const A2$ = "Revenue from Sale" Const A3$ = "Invoice" Const A4$ = "Sale" Const A5$ = "Credit Memo" Const A6$ = "A/R 902:UWW" Const A7$ = "A/R 902:Southeast - 902" Dim TypeOfInvoice As String, AccountName As String, DueDate As Variant,td As Variant Dim RS As New ResultSet,ay As Integer, am As Integer, ad As Integer Dim FileNum As Integer, i As Integer Set RS = CurrentDocument.Tables(0).CreateResultSet() '-----Start Process and Output QB Record Declaration type FileNum% = Freefile() td = Datevalue(currentview.body.MyList.text) ay%=Year(td) am%=Month(td) ad%=Day(td) A8$ = Format(Datenumber(ay%,am%,ad%),"mm-dd-yy") + ".iif" TableName$ = "j:\programs\accounting\qbexport\"+ A8$ Open TableName$ For Output As FileNum% Print #FileNum%, "!TRNS"&Chr(9)&"TRNSTYPE"&Chr(9)&"DATE"&Chr(9)&"ACCNT"&Chr(9)& _ "NAME"&Chr(9)&"AMOUNT"&Chr(9)&"DOCNUM"&Chr(9)&"CLEAR"&Chr(9)&"PONUM"&Chr(9)&"DUEDATE" Print #FileNum%,"!SPL"&Chr(9)&"TRNSTYPE"&Chr(9)&"DATE"&Chr(9)&"ACCNT"&Chr(9)& _ "NAME"&Chr(9)&"AMOUNT"&Chr(9)&"DOCNUM"&Chr(9)&"CLEAR"&Chr(9)&"INVITEM" Print #FileNum%, "ENDTRNS!" '-----Start Main Process Loop Do If rs.getvalue(34) = "CN" Then TypeOfInvoice = A5$ Else TypeOfInvoice = A3$ End If If Left(RS.GetValue(8),1) = "J" Then AccountName = A6$ Elseif Len(RS.GetValue(8)) = 3 Then AccountName = A7$ Else AccountName = A1$ End If td = RS.GetValue(9) 'Current system date ay%=Year(td) am%=Month(td) ad%=Day(td) Select Case currentview.body.terms.text Case Is = "Net 10" duedate = Datenumber(ay%,am%,ad% + 10) Case Is = "1% Net 10" duedate = Datenumber(ay%,am%,ad% + 10) Case Is = "Net 30" duedate = Datenumber(ay%,am%,ad% + 30) Case Is = "2% 10 Net 26" duedate = Datenumber(ay%,am%,ad% + 26) Case Is = "2%10 Net 26" duedate = Datenumber(ay%,am%,ad% + 26) Case Is = "2% 10 Net 30" duedate = Datenumber(ay%,am%,ad% + 30) Case Is = "2% 10 Net 30" duedate = Datenumber(ay%,am%,ad% + 30) Case Is = "COD" duedate = Datenumber(ay%,am%,ad%) Case Else duedate = Datenumber(ay%,am%,ad% + 30) End Select '-----Output Invoice TRNS LINE i% = i% + 1 Print #FileNum%, "TRNS" & Chr(9) & TypeOfInvoice & Chr(9) & Format(RS.GetValue(9),"mm/dd/yy") & Chr(9) & AccountName _ & Chr(9) & RS.GetValue(8) & Chr(9) & RS.GetValue(5) & Chr(9)& RS.GetValue(1) _ Chr(9) & "N" & Chr(9) & RS.GetValue(12) & Chr(9) & Format(duedate,"mm/dd/yy") '-----Output Invoice SPL LINE Print #FileNum%, "SPL" & Chr(9) & TypeOfInvoice & Chr(9) & Format(RS.GetValue(9),"mm/dd/yy") & Chr(9) & A2$ _ & Chr(9) & RS.GetValue(8) & Chr(9) & -RS.GetValue(5) & Chr(9) & RS.GetValue(1) _ & Chr(9) & "N" & Chr(9) & A4$ Print #FileNum%, "ENDTRNS" Loop While RS.NEXTROW Close FileNum% RS.Close Messagebox "Invoices Ready For Import Into QuickBooks. Open QuickBooks and Click on File / Import. Select file " + A8$, 0 + 64, i% &"Records Exported" End Sub