Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Variables from Access to Excel (office 97 on nt40)

    Is it possible to pass a variable from an access module or an Access text box on a form to an Excel macro. I am trying to write some code and create some queries which import from Excel into Access. I have tested successfully the process manually. I have created a form with the buttons I need to perform the various tasks. My problem comes into play because prior to the actual Transfer Spreadsheet and make table queries I need to launch a macro in Excel. The macro requires a file name to run properly. The file name changes routinely. I would like to pass that name to the macro from access. Is this possible? Hope this is enough info to adequately describe the issue. Thank You
    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variables from Access to Excel (office 97 on nt40)

    You can pass an argument, assuming you're actually running the Excel macro from Access. If you just want Excel to find something that lives in an Access variable, you're out of luck.

    Access and Excel are different domains, so a variable declared in one isn't available in the other, even if they're declared public.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Variables from Access to Excel (office 97 on nt40)

    As Charlotte suggests, you can use OLE Automation to run VBA in Excel. The subject is fairly complex, but you can get some samples from the MS knowledge base, and there is a good description of how to do Automation in the "Access Developers Handbook" by Litwin, Getz, et al
    Wendell

Posting Permissions

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