Omdat de gegevens in een platgeslagen ms-excel bestand aangeleverd mochten worden heb ik er voor gekozen om de export gecontroleerd via een aantal actie-queries te laten open.
Met onderstaand stukje VBA code worden tijdelijke tabellen geleegd en volgens TWINFIELD datastructuur weer gevuld.
Public Function exporteer_invoices() As Boolean
Dim db As DAO.database, qd As DAO.QueryDef, sSQL As String, rs As DAO.Recordset
Dim nummer As Integer
Dim MapBoekhouding As String
On Error GoTo foutafhandeling
Screen.MousePointer = 11
Set db = CurrentDb
exporteer_invoices = False
db.Execute ("delete * from tmp_bkh_nogNietGeexporteerd")
db.QueryDefs("qadd_bkh_NogNietGeexporteerd").Execute
db.Execute ("delete * from tmp_bkh_regels")
db.QueryDefs("qadd_bkh_regels_10").Execute
db.QueryDefs("qedt_bkh_regels_TotaalInclBTW").Execute
db.Execute ("delete * from tmp_bkh_TotaalInclBTW")
db.QueryDefs("qadd_bkh_totaalInclBTW").Execute
db.QueryDefs("qedt_bkh_FtotaalInBTW").Execute
Set rs = db.OpenRecordset("SELECT * FROM tmp_bkh_nogNietGeexporteerd order by invoicenumber")
nummer = 0
While Not rs.EOF
nummer = nummer + 1
rs.Edit
rs!number = nummer
rs.UPDATE
rs.MoveNext
Wend
db.Execute ("delete * from tmp_bkh_nogNietGeexporteerd_regels")
db.QueryDefs("qadd_bkh_nogNietGeexporteerd_regels").Execute
'union van kop + regels
db.Execute ("delete * from tmp_bkh_nogNietGeexporteerd_all")
db.QueryDefs("qadd_bkh_NNG_2all").Execute
db.QueryDefs("qadd_bkh_NNG_regels_2all").Execute
Set rs = db.OpenRecordset("SELECT * FROM tmp_bkh_nogNietGeexporteerd_all order by invoicenumber")
While Not rs.EOF
rs.Edit
If rs!regel = 0 Then rs!debitcredit = "debit"
If rs!regel = 1 Then rs!debitcredit = "credit"
rs.UPDATE
rs.MoveNext
Wend
db.Execute ("delete * from tmp_twinfield_facturen")
db.Execute ("delete * from tmp_twinfield_debiteuren")
db.QueryDefs("qadd_twinfield_facturen").Execute
'-- 2022.12.22
db.Execute ("UPDATE tmp_twinfield_facturen SET tmp_twinfield_facturen.Description = Left([description],35);")
db.QueryDefs("qadd_twinfield_debiteuren").Execute
db.QueryDefs("qedt_twinfield_debiteuren").Execute
'DoCmd.OpenQuery ("qry_bkh_xlsx_facturen")
'Exit Sub '
If InStr(1, UCase(Application.CurrentProject.Name), "XXX") > 0 Then MapBoekhouding = "F:\.......\private\boekhouding\XXX_"
If InStr(1, UCase(Application.CurrentProject.Name), "YYY") > 0 Then MapBoekhouding = "F:\.........\boekhouding\YYY_"
'MapBoekhouding = "U:\XXXXXXXXXX\private\boekhouding\"
*** FACTUUR INFO *********************************************************************************
SaveAs = MapBoekhouding & Format(Now(), "yyyymmdd_hhunn") & "_facturen.xlsx"
If Dir(SaveAs) <> "" Then Kill SaveAs
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_bkh_xlsx_facturen", SaveAs, yes
*** BIJBEHORENDE NAW DEBITEUREN *********************************************************************************
SaveAs = MapBoekhouding & Format(Now(), "yyyymmdd_hhunn") & "_debiteuren.xlsx"
If Dir(SaveAs) <> "" Then Kill SaveAs
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_bkh_xlsx_debiteuren", SaveAs, yes
'-- todo
'edit bkh_datum
exporteer_invoices = True
exit_here:
Screen.MousePointer = 0
Exit Function
foutafhandeling:
Screen.MousePointer = 0
MsgBox Err.DESCRIPTION
exporteer_invoices = False
End Function