smartsuitefaq.martinholz.de

Im Quickbooks-Dateiformat exportieren

Dies ist ein Beispiel in dem Daten in das Quickbook-Dateiformat exportiert werden. Der 'print #'-Aufruf gibt exact das aus, was Sie benötigen:

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:programsaccountingqbexport"+ 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
Über diesen ArtikelArtikelbewertung
Stand: 15.08.2001
Autor(en): Jerry Sikes, Martin Holz
engl. Original: www.netspace.net.au/~jabrown/approach/webfaq04302415.html
Durchschnittsnote: Dieser Artikel wurde noch nicht bewertet.
1 2 3 4 5 6