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.
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
Thanks in advance to all suggestions given.