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

[dwinn86]

:friends: 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? :think:

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

[SoftwareMatt]

Yes this is possible.
Here is code I use as a strting black:


Dim Path As String, x As Object
Path = "PATH\Excel Report.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QUERYNAMEHERE", Path
Set x = CreateObject("Excel.application")
x.Workbooks.Open FileName:=Path
x.Visible = True
Set x = Nothing