Originally Posted by
ajetrumpet
exporting access tables to multiple sheet in an excel book IS out on google, but I can guarantee you that the code your looking for, even semi-verbatim, for your "summary sheet" is not out there. No one is the same as you and no one has the same requirements.
googling what you need for the summary sheet will almost be impossible, so in all honesty, it's not really worth trying. the best thing to do for that would be to learn the ropes with vba and maybe via the recorder in excel.
for multiple sheets exporting, try googling "ms access export multiple excel sheets vba".
thanks for that explanation, I already feared that
However, I succeeded with the exporting to multiple sheets by using
Code:
Set xlWSCode = xlWB.Worksheets(1) 'deze wordt zometeen gevuld met zowel CodeI en Blancco_Code
xlWSCode.Name = "Code Verklaring" 'tabblad 1
Set xlWSTotals = xlWB.Worksheets(2) 'Totaal sheet (query wordt pas als laatste geschreven en tabblad pas als laatste gevuld).
xlWSTotals.Name = "Totalen" ' tabblad 2
'objXL.Visible = True
Fill_Code_Sheet CodeQuery, Blancco_CodeQuery, objXL, xlWSCode
Fill_Total_Sheet objXL, xlWSTotals, BoolVerschrotting
If BoolSysteem = True Then
xlWB.Worksheets.Add After:=xlWB.Worksheets(xlWB.Worksheets.Count)
xlWB.ActiveSheet.Name = "Systeem"
Set xlWSSysteem = xlWB.Worksheets("Systeem")
SysteemQuery = StrQuery & " AND Hardwaretype = 'Systeem' AND Tabblad = 'GETEST'" & OrderQuery
Set MyRec = CurrentDb.OpenRecordset(SysteemQuery)
If MyRec.RecordCount <> 0 Then
MyRec.MoveFirst
create_fields xlWSSysteem, objXL
Fill_Fields xlWSSysteem, objXL, MyRec
End If
End If
which is of course very specific to my dbase, but the intention is clear..
for every tab, there'll be a sheet, and thus a query.
I'm working on the Results tab just now..
kinda building everything around it and hoping that my brains will stay clever enough to find ways to do it ^^
one thing that really annoys me though is with the formatting.
Using Excel's Marco record function gives you some handles. but Excel uses all kinds of functions that are Excel specific (like
Code:
.HorizontalAlignment = xlCenter
to center text)
well.. try finding out what the MS Access VBA - code is for that. (turned out to be
Code:
.HorizontalAlignment = -4108
)