Results 1 to 12 of 12

Thread: Analyze (2003)

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Analyze (2003)

    When analyzing a report with excel, does anyone know if you can send it to an existing workbook as a new worksheet?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    Rob: When you analyze a report with excel it opens in excel as a new spreadsheet. Save it and you're good to go in excel.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyze (2003)

    No, you can't even specify the filename when you select Analyze with Excel. If you use File | Export..., you can at least specify the filename, but if there is an existing workbook with that name, it will be overwritten. You can export to a new file, and import the worksheet into an existing workbook, either manually or using Excel VBA.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    What I'm trying to do is minimise the work the user has to do. I know I could create a spreadsheet, save it. Create another one the week after and save that. Then compare the two. But if I could automate this in some way, it would be much easier and quicker for the person who has to print the report off.

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    It can be automated, but only using Excel VBA (as Hans says). You can write Excel VBA within Access by using object reference libraries so the user doesn't have to see or do anything extra - but there's no existing automation within Access to do what you need.
    Waggers
    If at first you do succeed, you've probably missed something.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    A simple File, Save As will work.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyze (2003)

    Where? How will that add a worksheet to an existing workbook?

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    My bad, I misunderstood the initial question.

  9. #9
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    Hi again Rob

    I don't know how experienced you are at using VBA so thought I should give you a step-by-step guide to achieve what you're after...

    1. <LI>From the database window, open a module. (Can be an existing one or a new one).
      <LI>Select Tools>References...
      <LI>Scroll down the list and make sure Microsoft Excel <version number> Object Library is selected, then press OK
      <LI>Copy the following into the VBA window:
      <div style="width: 100%; background-color: #FFFFFF;">Sub AddReportToWorkbook()
      Dim xl As Excel.Application, wb As Excel.Workbook, WorkBookName As String, _
      ReportName As String, ws As Excel.Worksheet, TempFileName As String

      If CurrentObjectType = acReport Then
      ReportName = CurrentObjectName
      TempFileName = CurrentProject.Path & "WoodysLoungeIsTheBest.xls"
      Set xl = New Excel.Application
      WorkBookName = xl.GetSaveAsFilename(, "Microsoft Excel Workbooks (*.xls),*.xls", , "Select workbook to add to")
      DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, TempFileName, False
      Set wb = xl.Workbooks.Open(WorkBookName)
      Set ws = xl.Workbooks.Open(TempFileName).Worksheets(1)
      ws.Move wb.Worksheets(1)
      wb.Save
      xl.Quit
      DoEvents
      Kill TempFileName
      End If
      End Sub</div hiblock>
      <LI>Hit the Save icon on the toolbar and close the VBA window.
      <LI>Back in the Access database window, right-click on the toolbar and select "Customise..."
      <LI>In the Toolbars tab, check the box next to the toolbar that displays for your report (usually "Print Preview" but you might use a custom-made toolbar instead)
      <LI>In the Commands tab, you should see "Custom" at the top of the list on the right-hand-side. Drag this across to the report toolbar.
      <LI>Right-click on the new Custom button on the toolbar, and select properties.
      <LI>Change the On Action property to "AddReportToWorkbook"
      <LI>Change the Caption property to "Add to Workbook..."
      <LI>Close the dialogue boxes.
    Now when you run your report, you should see your new "Add to Workbook..." button on the toolbar. Use this instead of the "Analyse with Excel" button; you'll be prompted for the name of your chosen workbook, and then the report will be added to the beginning of that workbook.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    Thanks Waggers. I'll give that a go. It looks as like what I want to happen. It's not required until next week, so I'll follow your instructions and let you know the results. Thanks to everyone for your help.

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Analyze (2003)

    I'm getting 'Microsoft Office can't run the macro or callback function 'AddReportToWorkbook' make sure the macro or function exists or takes the correct parameters.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyze (2003)

    Try changing 'AddReportToWorkbook' to a function, i.e. change

    Sub AddReportToWorkbook()

    to

    Function AddReportToWorkbook()

    If that doesn't help either, change the On Action property of your custom toolbar button to

    =AddReportToWorkbook()

    If that doesn't help either:
    - Make sure that you have created a procedure/function named AddReportToWorkbook in a module in your database.
    - Make sure that the name of the module is NOT equal to AddReportToWorkbook.

Posting Permissions

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