export to xml advanced (1 Viewer)

E

edojanssen

Registered User.
Local time
Today, 11:38
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?
 
B

Banana

split with a cherry atop.
Local time
Today, 02:38
Joined
Sep 1, 2005
Messages
6,318
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.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:38
Joined
Sep 12, 2006
Messages
15,634
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
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:38
Joined
Sep 12, 2006
Messages
15,634
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,133
Jupie23
J
A
Hide duplicate values from query in VBA while converting to XML
Replies
0
Views
1,324
aachave1
A
F
32-bit to 64-bit function coding conversion
Replies
6
Views
4,177
smig
S
E
export to xml
Replies
0
Views
1,452
edo janssen
E
M
Export to Txt (VBA Query)
Replies
1
Views
1,280
namliam
N

Users who are viewing this thread

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