export to xml advanced

E

edojanssen

Registered User.
Local time
Today, 08:31
Joined
Jun 21, 2006
Messages
23
I'm trying to make an export of invoices to xml so I can import it in our financial software. Desired format of the output:
- Invoice header
- Invoice lines
- Invoice Footer
- Next invoice header
- Invoice lines
- Inovice footer
- and so on

It went pretty well untill I got stuck. Code I got so far:
Code: 
'Private Sub Command33_Click()
Sub MaakXML19703()

DoCmd.OpenQuery "Query3"
DoCmd.OpenQuery "Query2"
 
Dim MyDB As Database
Dim MyRS As Recordset
Dim MyRS1 As Recordset
Dim strSQL As String
Dim strSQL1 As String
Set MyDB = CurrentDb

'Recordset voor ophalen faktuurkop
strSQL1 = "SELECT FactuurDatum, Boekstuknr, Dagboeknr, Faktuurnummer, Bedrag, Tegreknr, EntryGuid FROM Query3"
Set MyRS1 = MyDB.OpenRecordset(strSQL1)

'Recordset voor ophalen faktuurregels
strSQL = "SELECT Boekjaar, RekNr, FactuurDatum1, Periode, Dagboeknr, Tegreknr, Boekstuknr, Faktuurnummer, Bedrag, Debiteurnr, DebNaam, Kstplcode, Kstdrcode, Regel, EntryGuid FROM Query2 WHERE Boekstuknr = " & MyRS1!Boekstuknr & ""
Set MyRS = MyDB.OpenRecordset(strSQL)
 
Open "Verkoopboek2.xml" For Output Shared As #1
 
'------------------------------------FAKTUURKOP------------------------------------
Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<eExact xmlns:xsi=""http://www.w3.org/2001.XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""eExact-Schema.xsd"">"
Print #1, "<GLEntries>"
Do Until MyRS1.EOF
Print #1, "  <GLEntry>"
Print #1, "     <Description>" & MyRS1!Faktuurnummer & "</Description>"
Print #1, "     <Date>" & MyRS1!FactuurDatum & "</Date>"
Print #1, "     <DocumentDate>" & MyRS1!FactuurDatum & "</DocumentDate>"
Print #1, "     <Journal code=" & Chr(34) & MyRS1!Dagboeknr & Chr(34) & Chr(10) & "type=" & Chr(34) & "V" & Chr(34) & ">"
Print #1, "         <GLAccount code=" & Chr(34) & MyRS1!Tegreknr & Chr(34) & Chr(10) & "type=" & Chr(34) & "B" & Chr(34) & Chr(10) & "side=" & Chr(34) & "D" & Chr(34); ">"
Print #1, "             <Description>" & "Debiteuren ambachten" & "</Description>"
Print #1, "             <PercentageVATNonDeductable>" & "0" & "</PercentageVATNonDeductable>"
Print #1, "         </GLAccount>"
Print #1, "     </Journal>"
'------------------------------------FAKTUURREGELS------------------------------------
Do Until MyRS.EOF
Print #1, "     <FinEntryLine number=" & Chr(34) & MyRS!Regel & Chr(34) & Chr(10) & "type=" & Chr(34) & "N" & Chr(34) & Chr(10) & "subtype=" & Chr(34) & "K" & Chr(34) & Chr(10) & "transactiontype=" & Chr(34) & "0" & Chr(34) & ">"
Print #1, "         <FinYear number=" & Chr(34) & MyRS!Boekjaar & Chr(34) & "/>"
Print #1, "         <FinPeriod number=" & Chr(34) & MyRS!Periode & Chr(34) & "/>"
Print #1, "         <GLAccount code=" & Chr(34) & MyRS!reknr & Chr(34) & Chr(10) & "type=" & Chr(34) & "W" & Chr(34) & Chr(10) & "sybtype=" & Chr(34) & "J" & Chr(34) & Chr(10) & "side=" & Chr(34) & "C" & Chr(34) & ">"
Print #1, "             <Description>" & "Heffingen Aannemers" & "</Description>"
Print #1, "             <PercentageVATNonDeductable>" & "0" & "</PercentageVATNonDeductable>"
Print #1, "         </GLAccount>"
Print #1, "         <Description>" & MyRS!Faktuurnummer & "</Description>"
Print #1, "         <Costcenter code=" & Chr(34) & MyRS!Kstplcode & Chr(34) & "/>"
Print #1, "         <Costunit code=" & Chr(34) & MyRS!Kstdrcode & Chr(34) & "/>"
Print #1, "         <Debtor code=" & Chr(34) & MyRS!Debiteurnr & Chr(34) & Chr(10) & "type=" & Chr(34) & "C" & Chr(34) & ">"
Print #1, "             <Name>" & MyRS!DebNaam & "</Name>"
Print #1, "             <SecurityLevel>" & "0" & "</SecurityLevel>"
Print #1, "             <ExternalCode />"
Print #1, "             <CreditLine>" & "0" & "</CreditLine>"
Print #1, "             <SendReminder>" & "1" & "</SendReminder>"
Print #1, "             <PrintStatement>" & "0" & "</PrintStatement>"
Print #1, "         </Debtor>"
Print #1, "         <Amount>"
Print #1, "             <Currency code=" & Chr(34) & "EUR" & Chr(34) & Chr(10) & "active=" & Chr(34) & "True" & Chr(34) & ">"
Print #1, "                 <Description>" & "Euro" & "</Description>"
Print #1, "                 <VariableExchangeRate>" & "0" & "</VariableExchangeRate>"
Print #1, "                 <PrecisionRates>" & "0" & "</PrecisionRates>"
Print #1, "                 <PrecisionAmounts>" & "0" & "</PrecisionAmounts>"
Print #1, "                 <PrecisionPrices>" & "0" & "</PrecisionPrices>"
Print #1, "                 <InEMU>" & "0" & "</InEMU>"
Print #1, "             </Currency>"
Print #1, "             <Debit>" & "0" & "</Debit>"
Print #1, "             <Credit>" & MyRS!Bedrag & "</Credit>"
Print #1, "             <Value>" & "0" & "</Value>"
Print #1, "             <VAT code=" & Chr(34) & "0" & Chr(34) & Chr(10) & "type=" & Chr(34) & "B" & Chr(34) & Chr(10) & "vattype=" & Chr(34) & "E" & Chr(34) & ">"
Print #1, "                 <Included>" & "0" & "</Included>"
Print #1, "                 <Percentage>" & "0" & "</Percentage>"
Print #1, "                 <Charged>" & "0" & "</Charged>"
Print #1, "                 <GLToPay code=" & Chr(34) & "1801" & Chr(34) & Chr(10) & "type=" & Chr(34) & "B" & Chr(34) & Chr(10) & "subtype=" & Chr(34) & "C" & Chr(34) & Chr(10) & "side=" & Chr(34) & "D" & Chr(34) & ">"
Print #1, "                     <PercentageVATNonDeductable>" & "0" & "</PercentageVATNonDeductable>"
Print #1, "                 </GLToPay>"
Print #1, "                 <GLToClaim code=" & Chr(34) & "1802" & Chr(34) & Chr(10) & "type=" & Chr(34) & "B" & Chr(34) & Chr(10) & "subtype=" & Chr(34) & "C" & Chr(34) & Chr(10) & "side=" & Chr(34) & "D" & Chr(34) & ">"
Print #1, "                     <PercentageVATNonDeductable>" & "0" & "</PercentageVATNonDeductable>"
Print #1, "                 </GLToClaim>"
Print #1, "                 <Value>" & "0" & "</Value>"
Print #1, "                 <Creditor code=" & Chr(34) & "9" & Chr(34) & Chr(10) & "number=" & Chr(34) & "9" & Chr(34) & Chr(10) & "type=" & Chr(34) & "S" & Chr(34) & ">"
Print #1, "                     <Name>" & "Belastingdienst" & "</Name>"
Print #1, "                     <CreditLine>" & "0" & "</CreditLine>"
Print #1, "                 </Creditor>"
Print #1, "             </VAT>"
Print #1, "         </Amount>"
Print #1, "         <TransactionType>" & "0" & "</TransactionType>"
Print #1, "         <VATTransaction code=" & Chr(34) & "0" & Chr(34) & ">"
Print #1, "             <VATAmount>" & "0" & "</VATAmount>"
Print #1, "             <VATBaseAmount>" & MyRS!Bedrag & "</VATBaseAmount>"
Print #1, "             <VATPercentage>" & "0" & "</VATPercentage>"
Print #1, "             <VATForeignBaseAmount>" & "0" & "</VATForeignBaseAmount>"
Print #1, "             <ReportNumberCTListening>" & "0" & "</ReportNumberCTListening>"
Print #1, "         </VATTransaction>"
Print #1, "         <Payment>"
Print #1, "             <PaymentMethod code=" & Chr(34) & "B" & Chr(34) & "/>"
Print #1, "             <PaymentCondition code=" & Chr(34) & "30" & Chr(34) & Chr(10) & "installments=" & Chr(34) & "False" & Chr(34) & ">"
Print #1, "                 <DaysToPayment>" & "0" & "</DaysToPayment>"
Print #1, "                 <NumberOfMonths>" & "0" & "</NumberOfMonths>"
Print #1, "                 <DayOfTheMonth>" & "0" & "</DayOfTheMonth>"
Print #1, "             </PaymentCondition>"
Print #1, "             <Reference>" & MyRS!Faktuurnummer & "</Reference>"
Print #1, "             <XRateARAPPayment>" & "0" & "</XRateARAPPayment>"
Print #1, "             <XRateARAP>" & "0" & "</XRateARAP>"
Print #1, "             <TransactionNumberSubAdministration />"
Print #1, "             <OriginalARAPAmount>" & "0" & "</OriginalARAPAmount>"
Print #1, "             <CSSDDate1>" & MyRS!FactuurDatum1 & "</CSSDDate1>"
Print #1, "             <CSSDDate2>" & MyRS!FactuurDatum1 & "</CSSDDate2>"
Print #1, "             <CSSDYesNo>" & "B" & "</CSSDYesNo>"
Print #1, "             <CSSDAmount1>" & "0" & "</CSSDAmount1>"
Print #1, "             <CSSDAmount2>" & "0" & "</CSSDAmount2>"
Print #1, "             <InvoiceNumber>" & MyRS!Boekstuknr & "</InvoiceNumber>"
Print #1, "             <InvoiceDueDate>" & MyRS!FactuurDatum1 & "</InvoiceDueDate>"
Print #1, "         </Payment>"
Print #1, "         <FinReferences TransactionOrigin=" & Chr(34) & "N" & Chr(34) & ">"
Print #1, "             <ProcessNumberJournal>" & "0" & "</ProcessNumberJournal>"
Print #1, "             <UniquePostingNumber>" & "0" & "</UniquePostingNumber>"
Print #1, "             <YourRef>" & MyRS!Faktuurnummer & "</YourRef>"
Print #1, "             <DocumentDate>" & MyRS!FactuurDatum1 & "</DocumentDate>"
Print #1, "             <DebtorStatementNumber>" & "0" & "</DebtorStatementNumber>"
Print #1, "         </FinReferences>"
Print #1, "     </FinEntryLine>"
MyRS.MoveNext
Loop
'------------------------------------FAKTUURFOOTER------------------------------------
Print #1, "     <PaymentTerms>"
Print #1, "         <PaymentTerm type=" & Chr(34) & "K" & Chr(34) & Chr(10) & "status=" & Chr(34) & "C" & Chr(34) & Chr(10) & "ID=" & Chr(34) & MyRS1!EntryGuid & Chr(34) & Chr(10) & "paymentMethod=" & Chr(34) & "T" & Chr(34) & Chr(10) & "paymentType=" & Chr(34) & "B" & Chr(34) & ">"
Print #1, "             <GLOffset code=" & Chr(34) & MyRS1!Tegreknr & Chr(34) & Chr(10) & "type=" & Chr(34) & "B" & Chr(34) & Chr(10) & "side=" & Chr(34) & "D" & Chr(34) & ">"
Print #1, "                 <Description>" & "Debiteuren ambachten" & "</Description>"
Print #1, "                 <PercentageVATNonDeductable>" & "0" & "</PercentageVATNonDeductable>"
Print #1, "             </GLOffset>"
Print #1, "             <Amount>"
Print #1, "                 <Debit>" & MyRS1!Bedrag & "</Debit>"
Print #1, "                 <Credit>" & "0" & "</Credit>"
Print #1, "                 <Value>" & "0" & "</Value>"
Print #1, "             </Amount>"
Print #1, "             <ForeignAmount>"
Print #1, "                 <Currency code=" & Chr(34) & "EUR" & Chr(34) & Chr(10) & "active=" & Chr(34) & "True" & Chr(34) & ">"
Print #1, "                     <Description>" & "Euro" & "</Description>"
Print #1, "                     <VariableExchangeRate>" & "0" & "</VariableExchangeRate>"
Print #1, "                     <PrecisionRates>" & "0" & "</PrecisionRates>"
Print #1, "                     <PrecisionAmounts>" & "0" & "</PrecisionAmounts>"
Print #1, "                     <PrecisionPrices>" & "0" & "</PrecisionPrices>"
Print #1, "                     <InEMU>" & "0" & "</InEMU>"
Print #1, "                 </Currency>"
Print #1, "                 <Debit>" & MyRS1!Bedrag & "</Debit>"
Print #1, "                 <Credit>" & "0" & "</Credit>"
Print #1, "                 <Value>" & "0" & "</Value>"
Print #1, "                 <Rate>" & "1" & "</Rate>"
Print #1, "             </ForeignAmount>"
Print #1, "             <DaysToPayment>" & "0" & "</DaysToPayment>"
Print #1, "             <Percentage>" & "0" & "</Percentage>"
Print #1, "             <Reference />"
Print #1, "             <YourRef>" & MyRS1!Faktuurnummer & "</YourRef>"
Print #1, "         </PaymentTerm>"
Print #1, "     </PaymentTerms>"
Print #1, "</GLEntry>"
MyRS1.MoveNext
Loop
Print #1, "</GLEntries>"
MyRS1.Close
MyRS.Close
Set MyRS1 = Nothing
Set MyRS = Nothing
Set MyDB = Nothing
Print #1, "</eExact>"
Close #1

With this code I only get the header, lines and footer of the first invoice and the header and footer of the rest of the invoices. Who can help me out?
 
You didn't specify the version of Access but if you have Access 2003 or later, you can just use the builtin methods; look in VBA Editor help for those methods:

Application.ExportXML
Application.TransformXML

Exporting the XML will be in a certain schema so you may need to write a XSLT to transform the exported XML into the XML you want to send to your other software. There's good information on how to write a XSLT at W3C website.

HTH.
 
looks ok, but clearly isnt

I would put some msgboxes after each start of loop, to show you what you have got

on reflection i think you need this (i use while instead of do loop)

it looks like you possibly arent managing the inner loop correctly - is it being requeried for each change in the outer loop?

Code: 
while not rst.eof

[COLOR="Red"]     reopen recordset for rst1[/COLOR]
     while not rst1.eof
         dostuff
         rst1.movenext
     wend
     dostuff
     rst.movenext
wend
 
Banana said:
You didn't specify the version of Access but if you have Access 2003 or later, you can just use the builtin methods; look in VBA Editor help for those methods:

Application.ExportXML
Application.TransformXML

Exporting the XML will be in a certain schema so you may need to write a XSLT to transform the exported XML into the XML you want to send to your other software. There's good information on how to write a XSLT at W3C website.

HTH.
Click to expand...

I will have a look at that. I need a decent tutorial for XLST's. I couldn't find one, so I generate XML's with brute force coding!
 
You must log in or register to reply here.

Similar threads

J
Exporting reports to PDF slow
Replies
7
Views
3,372
Jupie23
J
F
32-bit to 64-bit function coding conversion
Replies
6
Views
4,315
smig
S
A
Hide duplicate values from query in VBA while converting to XML
Replies
0
Views
1,353
aachave1
A
E
export to xml
Replies
0
Views
1,487
edo janssen
E
M
Export to Txt (VBA Query)
Replies
1
Views
1,314
namliam
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom