Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Provided Answers: 5

    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.
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    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"
        AppXL.Workbooks.Open FileName:=strFileName
        ' Make Sheet1 the active sheet
        ' etc.
        For Each CellName In AppXL.ActiveWorkbook.Names
        ' . . .
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts