Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Smile Unanswered: Automation Question to Excel

    I have three queries that hold daily, monthly and yeartodate information.. What I do now is manual but am looking for a way to automate this process, of taking the 3 queries, export to Excel WS, automatically create 3 pivot tables, in Excel, then create the master report with the 3 pivot table information...

    I want the 3 tables to go on the same sheet if possible.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is an example about how to work with Excel from Access.
    Code:
    Function ExcelGuideLines()
    
        Const c_strXLSFile As String = "C:\Documents and Settings\SinnDHo\My documents\Access\CF_Sit\CF_Sit_2\Billing\Ack_19005.xls"
        Const c_strXLSNewFile As String = "C:\Documents and Settings\SinnDHo\My documents\Access\CF_Sit\CF_Sit_2\Billing\Ack_19005.xls"
        
        Dim appXL As Excel.Application
        Dim rst As DAO.Recordset
        
        ' Create an instance of Excel.
        '
        Set appXL = New Excel.Application
        
        ' By default, the instance of Excel is not visible.
        ' It's better to see what happens when in development.
        ' Later on you can comment this line.
        '
        appXL.Visible = True
        
        ' This will open an existing Excel workbook (Workbook = .xls file).
        '
        appXL.Workbooks.Open Filename:=c_strXLSFile
        
        ' While this will create a new workbook.
        '
        appXL.Workbooks.Add
        
        ' Save the new workbook giving it a name.
        '
        appXL.ActiveWorkbook.SaveAs Filename:=c_strXLSNewFile
        
        ' We can select an existing sheet in the Excel workbook.
        '
         With appXL
            .Sheets("Sheet1").Select
            
        ' Or we can create a new sheet.
        '
            .Sheets.Add
            .ActiveSheet.Name = "Dependencies"
            .ActiveSheet.Move After:=.Sheets(.Sheets.Count)
        End With
        
        ' Write something in a cell in the selected sheet
        ' and format it.
        '
        With appXL
        
            ' Select the cell.
            '
            .Range("B1").Select
            
            ' Write something in it.
            '
            .ActiveCell.FormulaR1C1 = "Dependencies in database:"
            
            ' Merge cells "B1" to "D1" and format the contents.
            '
            .Range("B1:D1").Select
            With .Selection
                .Merge
                .Font.Name = "Arial"
                .Font.Size = 14
                .Font.Underline = xlUnderlineStyleSingle
            End With
            .Rows("1:1").RowHeight = 25
        End With
        
        ' Open a recordset based on a query ("Query_1") and export its contents
        ' beginning at a given cell address ("A5") in the selected sheet.
        '
        Set rst = CurrentDb.OpenRecordset("Query_1", dbOpenSnapshot)
        appXL.Range("A5").CopyFromRecordset rst
        rst.Close
        Set rst = Nothing
    
        ' Save the workbook and close it.
        '
        appXL.ActiveWorkbook.Save
        appXL.ActiveWorkbook.Close
        
        ' Close the instance of Excel: This is very important !
        ' Setting appxl to nothing does not close Excel.
        '
        appXL.Quit
        
        ' Clean memory when done.
        '
        Set appXL = Nothing
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thank you! I tried this and got it to work!

Posting Permissions

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