Exporting data from MS-Access to Excel

[JimP]

...need some help (a reference, or book, that will explain how to export data
from Access to Excel) to create a formatted report with sub-totals.

e.g. this is an employee timesheet application that will export data from an
Access query into what must eventually be a report with department and
employee groupings and sub-totals of hours, with drill down on the
sub-totals.

I know how to do the "TransferSpreadsheet" method to get the data into
Excel, but how do I get it into a usable form once it is in Excel.

My guess is it must go into a "data worksheet" which then loads into a
separate "report worksheet", with column headers, sub-totals, formatting
etc.

The "data worksheet" will be updated monthly and the number of rows will
vary each time the report is run.

 

[ryguy7272]

Do you know how to subtotal in Excel? If so, turn on the macro recorder,
then go through the steps in Excel. Turn off the macro recorder when you are
done. Take that code and pop it in to this macro (which must be run from
Access):

Option Compare Database



Option Explicit ' Use this to make sure your variables are defined



' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet





Sub Rep()



Dim strFile As String



strFile = "C:\path-to-your-file.xls"



‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible



Set objExcel = New Excel.Application



objExcel.Visible = True



' Opens up a Workbook



Set xlWB = objExcel.Workbooks.Open(strFile)







' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.



Set xlWS = xlWB.ActiveSheet



' Set xlWS = xlWB("Sheet1")





With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…put your subtotaling Excel Macro right here!!



End With



' Close and Cleanup



xlWB.SaveAs xlSaveFile

xlWB.Close



xlapp.Quit

Set xlapp = Nothing



End Sub

You are trying to control Excel from Access, right. That’s how I
interpreted your post. Again, this code is in Access; it needs to be run
from Access.

Regards,
Ryan---

 

[JimP]

Thanks,

I am familiar with sub-totals and will give the code a try. Yes, I am trying
to run this from Access - But it occurred to me that it might be easier to
run it from Excel.

 

[JimP]

One question though.

It looks like this code will start my data range in cell A:1 in the named
worksheet. What if I have report and column headers already set up on the
named worksheet. Is there a way to start my exported data range in a cell
other than A:1?

 

[ryguy7272]

Sure, set it up in Excel, then copy/paste your Excel code right below this
line and run your Access macro. You will be amazed when you see what
happens. Just try it and you'll see.

One more thing, set a reference to Excel in Access.
Alt + F11 > Tools > References > Microsoft Excel xx.x Object Library

Regards,
Ryan---