Exporting to Excel using an Access query

Hi everyone,

I'm hoping someone could give me some direction as it's been a few years since I've fired up Access.

I'm building a 5-year financial model for an energy company. The kind of information includes the amount of energy produced, and the price it is sold for, for each individual power station during every half-hour of each of the five years. For example:

1/1/2012 08:00 Gen A 88MWh Gen B 140 MWh
1/1/2012 08:30 Gen A 100MWh Gen B 120 MWh

They originally wanted it in Excel, but 17520 rows * 200 cost/revenue items * 5 years was causing it to start crashing - I convinced them to accept Access in the background as a database with Excel providing the user interface/output template.

We want an Excel interface such that aggregated monthly information is retrieved from the database, so for example the sum of each half-hour's generation during Jan for Gen A is displayed, and the sum for Gen B, then repeat it for Feb, Mar etc.

Jan Feb Mar
Gen A GWh 72 72 72
Gen B GWh 80 80 80
Gen A Gas Tj 720 720 720
Gen B Gas Tj 800 800 800
Gen A Revenue $m 5.6 5.6 5.6
Gen B Revenue $m 6.3 6.3 6.3

I was thinking it could be best to retrieve the monthly info and dump it all into a new tab on the Excel interface, which wouldn't be too much information for Excel. Then it could automatically populate a P&L, revenue graphs, gas contract volume graphs, etc.

My questions are, is my idea of exporting the aggregated data to a tab in the Excel interface and then manipulating it the most direct/effective way? Am I best off creating a simple query to do this? What is the best way to embed this query in Excel? and are there any functionality issues of which I should be aware?

Many thanks in advance and best regards,

Paul McCarthy

Welcome to the Forum Paul,

1 idea is to create a query in Access and draw all the fields in and make that work to start with in the way you want. You can then use some code to get the query into Excel. The following code would help you out getting the query into the spreadsheet. You would need to add the reference in Excel VBA screen to Access and DAO.

Excel VBA Query Access Database return results

Sub queryAccess()
'Add the reference for MS Access and Microsoft DAO
'Go To Tools References search down the list for Microsoft Access XX.Object Library
'Tick the box and do the same for Microsoft DAO XX.Object Library
Dim appAcc As New Access.Application
Dim rst As DAO.Recordset
Dim ws As Worksheet
Dim i As Long
Const path As String = "M\Training.mdb"
Set ws = ThisWorkbook.Sheets("sheet2") 'Change this to the required sheet name
With appAcc
.opencurrentdatabase path
Set rst = .CurrentDb.OpenRecordset("SELECT * FROM qryExcel")
For i = 0 To rs.Fields.Count - 1
ws.Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
ws.Range("A2").CopyFromRecordset rst
End With
appAcc.Quit
Set appAcc = Nothing
End Sub

Click to expand...

Thanks Trevor,

I posted a reply yesterday but it seems to not be showing - so again, thank you very much for your prompt and helpful assistance!

Your welcome Paul and thanks for the reply.

Trevor G said:

Welcome to the Forum Paul,

1 idea is to create a query in Access and draw all the fields in and make that work to start with in the way you want. You can then use some code to get the query into Excel. The following code would help you out getting the query into the spreadsheet. You would need to add the reference in Excel VBA screen to Access and DAO.

Click to expand...

Trevor -

This is just a suggestion. You might want to make your code reusable (like what I have on my web page for Exporting to Excel) instead of hard coding in values, or even suggesting that values should be hard coded.

That is why I posted the code on my website the way I did so people can just drop it into a standard module and run with it. No modifications really necessary (except maybe for some formatting pieces depending on preferences).

So, when you are writing code, work at trying to come up with a way that it can be used over and over again from different places without having hard coded values in it. Again, just a suggestion.

Hello Bob,

I will take a look at your suggestions.

Always willing to review methods.