Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    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
    Posts
    5,442
    Provided Answers: 14
    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.
    Code:
        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
        '
        AppXL.Sheets("Sheet1").Select
        ' 
        ' etc.
        '
        For Each CellName In AppXL.ActiveWorkbook.Names
        ' . . .
        Next
        '
    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
  •