Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: use excel sheet as report

    I have an access database. They need create a spread sheet from database. Is that possible to create excel directly from access database. I know we can create report then use office link to excel. Could you please help me how to do it? Thanks a lot.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try looking up excel export in help.

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    You can also check out Poot's post in the Code Bank for some useful tips.
    Me.Geek = True

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You can also lookup the TransferDatabase method or OutputTo method in the help file.

    Sam

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Or you can just turn on the Excel Object Library in references and start coding. I have a few items where a standard access report wouldn't do, so I have my app "build" a spreadsheet from scratch. Data, formulas, formatting, it's all done via VBA.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    Thanks for all you help. Now i want to explain what i need. I need a report such as the following:

    Categories1 Categories2 Categories3 Categories4
    ReceiptID ReceiptFrom

    1 aa $20
    2 bb $30
    3 cc $10
    4 dd $40
    5 ee $20

    GrandTotal $60 $10 $30 $20



    I tried to use pivottable. But it shows different as they treat Receipt From as two row with a total.

    Is that best way to create a form like this and can be create in excel.

    Thanks.
    Last edited by yyu; 07-29-07 at 10:25.

  7. #7
    Join Date
    Mar 2004
    Posts
    660
    Thanks for all you help. Now i want to explain what i need. I need a report in the attachment (book1):

    In this report, there are ReceipID and ReceiptFrom as rows, Categories1, ... Categories4 as colums. Each receipt will show up on the report. Different receipt ID with particular categoires. At the end, we need grandtotal.


    I use pivottable, it look like pivottable in the attachment:

    Is that a way to create a form like book1? Many thanks.
    Attached Files Attached Files
    Last edited by yyu; 07-29-07 at 10:43.

  8. #8
    Join Date
    Jun 2007
    Posts
    74

    Excel Automation

    Most of my VBA experience is in Excel. I like the idea of adding the reference to the Excel library and "coding away" as Redneck suggested. Does anyone have any code examples they can post? Specifically for opening Excel? Or maybe a methodology... Do I open Excel, and then use VBA to Format cells and enter data cell by cell? Onec I get started I am sure I will breeze through. I am just stuck on getting started.

    Please Help

  9. #9
    Join Date
    Mar 2004
    Posts
    660
    Here is my code to export the query to excel sheet. In Tool - Reference, you need add Microsoft Excel 10.0 object library (this is my version). Hopefully this can help.

    Function Deposit()

    On Error GoTo Err_DailyDeposit


    DoCmd.TransferSpreadsheet acExport, 8, "qryDeposit", "C:\Deposit.xls", True


    Exit_Deposit:
    Exit Function

    Err_Deposit:
    MsgBox Err.Description
    Resume Exit_Deposit

    End Function
    Last edited by yyu; 08-16-07 at 12:55.

  10. #10
    Join Date
    Jun 2007
    Posts
    74
    Thank you for the response. But this is really a different method. I am not exporting a query. I have a form that allows the user to select various (Numerous) criteria for report generation. In my situation, Ms Access Reports are not usefull for the reports I need to generate.

    So I want the users to be able to select the criteria for the report on my form and then when a "Submit" button is pressed I will retrieve all of the needed data. In most instances will be calculated at runtime and not stored in the db. Once I generate the data I need to open Excel, format cells, and enter the data directly. If I can figure out how to open excel, I should be able to handle the rest.

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    isn't the same question as in the other thread?
    Ryan
    My Blog

  12. #12
    Join Date
    Mar 2004
    Posts
    660
    Quote Originally Posted by Brent Blevins
    Thank you for the response. But this is really a different method. I am not exporting a query. I have a form that allows the user to select various (Numerous) criteria for report generation. In my situation, Ms Access Reports are not usefull for the reports I need to generate.

    So I want the users to be able to select the criteria for the report on my form and then when a "Submit" button is pressed I will retrieve all of the needed data. In most instances will be calculated at runtime and not stored in the db. Once I generate the data I need to open Excel, format cells, and enter the data directly. If I can figure out how to open excel, I should be able to handle the rest.
    This is also what i want to learn. hopefully somebody can help us. That is why i have to create a query, then export the query to excel.

  13. #13
    Join Date
    Mar 2004
    Posts
    660
    Quote Originally Posted by rguy84
    isn't the same question as in the other thread?
    Other is for the swtichboard manager. i want to export the query to excel using switchborad manager.

  14. #14
    Join Date
    Jun 2007
    Posts
    74

    So far this is what I am working with

    I found this code in an old post and am trying to modify it for my use. My button click code calls FeedbackReportMaster and then FeedbackReportMaster is calling FRGEN. I know it could be combined but I need it separated as I will be calling different functions based on selections made on the form for report generation.

    Since I am not passing the name of an existing Workbook, the ProcError is invoked.but appears to not create a new file. When it reaches "objXLWb.SaveAs strWorkBook" I get an error. "Picture Attached". I am not sure where to go from here.




    Code:
    Function FeedbackReportMaster()
    Dim strDT As String, strYR As String, strMO As String, strDY As String, strPath As String, strExt As String
    Dim appXL As New Excel.Application
    
        'Set the path info
        strDT = Date
        strDT = Format(strDT, "YYYYMMDD")
        strPath = "C:\Program Files\vibePublisher\Reports\Feedback Report " & strDT & ".xls"
        'Call FRGEN to create the excel file
        Call FRGEN(strPath)
    
    
    End Function
    
    Function FRGEN(strWorkBook As String)
    Dim objXLApp As Object 'Excel.Application
    Dim objXLWb As Object 'Excel.Workbook
    Dim objXLSheet As Object 'Excel.Worksheet
    Dim strWorkSheet As String
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim i As Integer
    
    On Error GoTo ProcError
    
    DoCmd.Hourglass True
    
    
    'Start Excel
    Set objXLApp = CreateObject("Excel.Application")
    
    'Open workbook, error routine will 'create it if doesn't exist
    Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
    
    'Select a worksheet, if sheet doesn't exist 'the error routine will add it
    If strWorkSheet = "" Then
        strWorkSheet = "Sheet1"
    End If
    
    
    
    ProcError:
    
        Select Case Err
            Case 9 'Worksheet doesn't exist
            objXLWb.Worksheets.Add
            Set objXLSheet = objXLWb.ActiveSheet
            'objXLSheet.name = strWorkSheet
        
            Resume Next
        
            Case 1004 'Workbook doesn't exist, make it
            objXLApp.Workbooks.Add
            Set objXLWb = objXLApp.ActiveWorkbook
            objXLWb.SaveAs strWorkBook
            
            Resume Next
        
        Case Else
            DoCmd.Hourglass False
            MsgBox Err.Number & " " & Err.Description
            Stop
            Resume 0
        End Select
    
    
    End Function
    Attached Thumbnails Attached Thumbnails SaveError.bmp  
    Last edited by Brent Blevins; 08-16-07 at 14:33.

  15. #15
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Brent, since your issue is different, why don't you make your own thread.
    Ryan
    My Blog

Posting Permissions

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