Unanswered: Object Web Components and exporting to Excel
Hello people. It has been awhile ince I participated. Hello Izy, Geo et al
I have a table in access that is derived from a several nested queries
the table is tbl_COST_REPORT. I want to export that table to excel and then from VBA within access create a 2nd worksheet(tab) that takes the raw data and makes a pivot table. The field names I want to pivot on are:
[Project Name], [Project Manager], [Resource Name] as my rows and Period (Dates) as my column and totalling Hours and Manhour_Cost
I do not need drill down specific code (that would be nice ) but pseudo code would be a great start.
You can do this using Automation. You create a reference to an Excel object in Access (in VBA), then you can issue commands to the Excel instance you created.
Dim AppXL As Excel.Application
Dim BookXL As Excel.Workbook
Dim CellName As Excel.Name
Dim strFileName as String
' Create a reference to Excel and make it visible.
Set AppXL = CreateObject("Excel.Application")
AppXL.Visible = True
' Use the instance of Excel to open a .xls file
strFileName = "SomeExcelFile.xls"
' Make Sheet1 the active sheet
For Each CellName In AppXL.ActiveWorkbook.Names
' . . .