Export from Access to Excel

[Eowyne]

Well, since i'm here already posting one of my two questions.. I might as well post the other one:

My company wants me to create a form on which an employee can export some records from the database.
This is not the problem.

The problem is the fact that i would like to format the Excel database in the following order:

Sheet1: Summary of all the other tabs
Sheet2: (if exists) Systems


Sheet3: (If Exists) Monitors
Sheet4: (If Exists) Laptops
and so on.

Putting the Sheet's 2-4 in the excel database should be doable with running multiple query's based on a first one which selects which tabs there should be added. However, the first sheet must contain a summary of the other sheets.

Is there a way to export both the data, and insert formula's and plain text into a sheet?

P.s. I'm a complete newbie to exporting to Excel. I Thought it would be easy since Access already facilitates this.. however.. Writing the actual VBA code is as challenging as the first time I wrote a Inner join query ^^

[ajetrumpet]

Is there a way to export both the data, and insert formula's and plain text into a sheet?

are you talking about doing this FROM access?? the only way to do stuff like this is from code. the interface in access can only do simple export functions. code is required for complex tasks. This is very tough to figure out, but for a developer it would only be a simple case of trial and error to make sure that the code actually works.

You can obviously export multiple tables to multiple sheets in the same excel workbook, but to my knowledge, code is required to do this as well, unless you want to do one table at a time, manually.

[Eowyne]

Yes, it will involve a lot of VBA codeing, but that is not the problem, my problem is that I'm new to the whole export function. and am wondering if there's help out there somewhere so that I don't need to re-invent the wheel for things already done

[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".

[Eowyne]

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

    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

.HorizontalAlignment = xlCenter

to center text)
well.. try finding out what the MS Access VBA - code is for that. (turned out to be

.HorizontalAlignment = -4108

)

[pkstormy]

Have you thought of creating a macro in excel itself which does the summing and then call that macro in your MSAccess application (I'm pretty sure you can execute an excel macro via vba in MSAccess). The nice thing with excel macros is that you can easily create them using the 'record' setting.

For an example of exporting in MSAccess, I have this example:
https://www.accessforums.net/code-re...mple-7555.html
and
https://www.accessforums.net/code-re...mple-7571.html

I usually just use the docmd.transferspreadsheet to avoid creating an excel object. But in your case with the adding worksheets, this may not be an option for you.