Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004

    Unanswered: Run Access macro with Excel macro

    I currently maintain a complicated Excel form that is used by our company's sales reps. After pricing is accepted, the saved version of form is placed in a specific folder for my retrieval. I also maintain the Access db for the critical data contained in the forms. My daily routine is to open the Excel form and run a VBA Excel macro that pulls critical data from the form and places it in a simple row and column format for transfer into database. The last step of the Excel macro contains the following line allowing me to activate Access.
    Application.ActivateMicrosoftApp xlMicrosoftAccess

    If my database file was not previously open the dialog box appears allowing me to choose the database file I want opened. If the database was previously open, Excel minimizes and the Access database window maximizes.

    I then click a command button on the form to activate the transfer macro in Access transferring 3 spreadsheets of data into 3 Access tables. This database is the front end where the 3 tables are actually linked to SQL Server.

    Due to the daily high volume of files, I am trying to reduce keystrokes and/or mouse clicks by activating the Access macro from Excel. The process now includes a click to bring up the open dialog box (or Ctrl+O) in Excel, double click to open file, click toolbar button (or Alt+0) to run Excel macro, click command button to run Access Macro and repeat over and over. I would ideally like to open the file and run the whole Excel/Access process in one step from the Excel toolbar button (or Alt+0). I've been searching the forum and found some code I thought was close (see below), but just can't get it to work. Hoping someone can tell me if this scenario is possible and give me some direction as to how to accomplish this.

    Dim AF As Object
    Set AF = GetObject("server or drive:\filename.mdb")
    AF.Application.Visible = True
    AF.Application.Windows("frmTransferDecember").Visi ble = True
    AF.Application.Run "mcrTransferDecember"

    Thanks in advance to all suggestions given.

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    you could transfer your access code in excel, add the references you need, i.e. the same ones from access and fully qualify your arguments,

    for your open file you can use
    and pass your string to the mscro to open the .mdb file

    i can go into more detail but for now, im going home


Posting Permissions

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