Results 1 to 6 of 6
  1. #1
    nwood is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    7

    VBA to run Excel files and click buttons (with macros) in Access


    Hello,

    I am working on automating something where I use a combination of Excel & Access. Here's a small breakdown of the structure of it.

    Button in an Access form that runs the following code through VBA
    • Runs Excel Macro in workbook A
    • Click (2) buttons in Access form (same form as my button that runs everything) that contains Delete & Update queries respectively
    • Run Excel Macro in workbook B
    • Run Excel Macro in workbook C


    I have figured out how to run macro in workbook A from Access (and probably will be able to in B & C as well), but I am having a problem with the VBA code used to click the two buttons. After running Excel macro in workbook A from Access, what is the code to click the two buttons in my form to run the Access queries?

    Appreciate the help!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming there's VBA behind the buttons (as opposed to macros) you can call the button code after making it public, but what I would do is create a function and call it from both the buttons and this code. Personal preference, but calling the button code strikes me as "spaghetti code".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    nwood is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    Presuming there's VBA behind the buttons (as opposed to macros) you can call the button code after making it public, but what I would do is create a function and call it from both the buttons and this code. Personal preference, but calling the button code strikes me as "spaghetti code".
    The buttons actually have macros behind them (openqueries to be specific). I am new to access VBA and Excel macros/VBA are pretty interchangeable, so I am a little confused.

    How would I go about this by trying to get the VBA to click the buttons that run macros? Appreciate the help!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    what is the code to click the two buttons in my form to run the Access queries?
    This is confusing - code does not click buttons, users do. In the OnClick event of a button either a subroutine (VBA) or a macro will be run. Both can run a query.

  5. #5
    nwood is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    7
    Quote Originally Posted by aytee111 View Post
    This is confusing - code does not click buttons, users do. In the OnClick event of a button either a subroutine (VBA) or a macro will be run. Both can run a query.
    I was trying to get the VBA to click the buttons, thus running the macros.

    But I figured it out. I just wrote code to run the queries how I do in the macro, so no need for code to click buttons anymore.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For the record, you can programmatically click the button by calling it. Don't have Access running, but along the lines of:

    ButtonName_Click()

    But like I said, it's not the way I'd go. Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 01-08-2015, 06:16 AM
  2. Replies: 4
    Last Post: 05-15-2014, 12:49 PM
  3. Run Excel Macros in Access
    By jo15765 in forum Programming
    Replies: 8
    Last Post: 10-13-2011, 01:20 PM
  4. Using Excel macros in Access
    By Lorlai in forum Programming
    Replies: 2
    Last Post: 06-03-2011, 02:01 PM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums