Call VBA module to create spreadsheet along with SQL query to populate spreadsheet

[dwinn86]

Hello everyone,

My name is Dan.

I have created a module in VBA that creates an Excel spreadsheet when executed (mdlExcelCreation).

I have also created an SQL query, that selects records from a view (qrySpreadsheetData).

I am at the stage now where I wish to combine the two, so that a spreadsheet gets created and the records rectrieved from the SQL query are put in to the spreadsheet.

Is this possible at all?

I am thinking about creating a new module that when called, executes the module (mdlExcelCreation), and then executes the SQL query, but I am not sure if this will work.

Could anybody advise on this please on the best way to accomplish this.

Many thanks and regards,

Dan

 

[Guus2005]

Hi Dan, my name is Guus. Welcome!

First, a module is not something you execute. A procedure with no return value or a function which returns a value are used to execute code. A module stores procedures and functions among other things.
Having said that, Yes. What you want can be done using Access.

The following code will create an excel object and exports a query to it.

Public Sub CreateXLAndExportQuery(strQuery As String)
    
    Dim dbs   As Database
    Dim rst   As Recordset
    Dim appXL As Excel.Application
    Dim wb    As Workbook
    Dim ws    As Worksheet
    
    Set appXL = New Excel.Application
    Set wb = appXL.Workbooks.Add
    Set ws = wb.Worksheets(1)
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strQuery)
        
    ws.Range("A1").CopyFromRecordset rst

    appXL.visible = true

End Sub

HTH

 

[dwinn86]

Hello Guus,

Thank you very much for getting back to me and thank you for that

Is it possible to take this further and put a loop in so that when it runs, it can go through 48 tables that I have got and produce individual spreadsheets for these tables.

Each table you see is for a different educational establishment.

Each table therefore has a unique number.

I want it so that my code can loop through all of my tables using this unique number and putting the records in to indiviual spreadsheets.

For example: centres with number 908 go in to one spreadsheet, the centres with 909 go in to another etc etc.

Does this make sense?

Also, is there a way to do this?

Many thanks,

Dan

 

[Guus2005]

Yes, there is.
You can loop thru the tables using the TableDefs collection

dim tdf as tabledef

for each tdf in currentdb.tabledefs
' do your thing
    debug.print tdf.name
next tdf

to combine the two pieces of code:

Public Sub CreateXLAndExportAllTables()
    
    Dim dbs   As Database
    Dim rst   As Recordset
    Dim appXL As Excel.Application
    Dim wb    As Workbook
    Dim ws    As Worksheet
    Dim tdf   As TableDef
    
    Set dbs = CurrentDb
    Set appXL = New Excel.Application
    
    For Each tdf In CurrentDb.TableDefs
        Set wb = appXL.Workbooks.Add
        Set ws = wb.Worksheets(1)
        Set rst = dbs.OpenRecordset(tdf.Name)
            
        ws.Range("A1").CopyFromRecordset rst
    
        appXL.Visible = True
        wb.SaveAs Environ("TEMP") & "\" & tdf.Name & ".xls"
        wb.Close
    Next tdf

    Set appXL = Nothing

End Sub

You will notice that Excel is flinkering in the foreground.
Set appXL.Visible to False to suppress Excel.

Do that *after* setting it to true.

appXL.Visible = True
appXL.Visible = False

If you don't make Excel visible you will not see Excel in your taskbar and only in the taskmanager. If something goes wrong you will have to kill the proces using the taskmanager. After maing it visible you can simply close it from the taskbar.

HTH

 

[dwinn86]

Hi Guus,

Thank you very much.

Sorry I have explained it wrong.

I have a view that selects data from a database depending on the CentreNumber.

So the 48 views can be created from this query by running the query with a different CentreCode each time.

Do you understand what I mean?

I want to run a code so that a new spreadsheet is created each time it comes across a new CentreCode.

Is this possible?

Thanks,

Dan

 

[Guus2005]

Yes, again it is.

Create a recordset sorted on the code you seek and execute the code to create the spreadsheet according to your specifications.
Walk thru the recordset and when you find a different code run the procedure to create a different spreadsheet.

Piece of cake.