Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Unhappy Unanswered: ACCESS SETUP to use EXCEL

    HI

    Using ACCESS 2002, creating reports to EXCEL but want to have an EXCEL MACRO run before opening the EXCEL spreadsheet.

    I have create a Module with the following:

    Sub mdlRunExcelCode()

    Dim XL As Excel.Workbook

    (Note: When I was typing this: EXCEL DID NOT APPEAR as an option in the drop down box that did appear).

    Set XL = GetObject("c:\test\test.xls", "Excel.Sheet")
    XL.Application.Visible = True
    XL.Application.Run "PERSONAL.xls!FixExcel"

    End Sub

    And this in the FORM

    DoCmd.OutputTo acOutputQuery, stDocName, _
    acFormatXLS, "c:\test\text.xls"
    MsgBox ("Completed")

    Call mdlRunExcelCode

    My PROBLEM IS:

    Getting a Visual Basic Error:
    Compile Error:
    User-defined type not defined

    As if ACCESS or VBA does know about EXCEL.

    But I don't know where to go from here to add a component or whatever is needed for ACCESS to know about EXCEL.

    Also, the Analyze with EXCEL does work OK.

    I'm working on a server and do not have any installation CD's.

    Any help appreciated.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    From a module, go to Tools:References and make sure Microsoft Excel Object Library is checked
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Smile Thanks.

    That was what I needed but did not know where to look.

Posting Permissions

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