Macros & Auto updates in ACCESS & EXCEL

[Suzette]

I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables.

I created a macro in Access to perform various necessary functions with the data.

I want to be able to open an Access form that I created, and then hit a button that will run the macros and that will open the excel database.

The only problem that I am having is that when the excel file opens and tries to update, it can't because the access database is still open...

I need to make this user friendly for another colleague.

Is there any way to solve this one? Aside from opening access, running the macros manually, closing access and then opening excel?

I was wondering if it is possible to have the macro run the updates, close the database and then open the excel file automatically...

Thanks in advance for your help!!

[Suzette]

Forgot to mention I have MS Access 2003

[ADezii]

I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables.

I created a macro in Access to perform various necessary functions with the data.

I want to be able to open an Access form that I created, and then hit a button that will run the macros and that will open the excel database.

The only problem that I am having is that when the excel file opens and tries to update, it can't because the access database is still open...

I need to make this user friendly for another colleague.

Is there any way to solve this one? Aside from opening access, running the macros manually, closing access and then opening excel?

I was wondering if it is possible to have the macro run the updates, close the database and then open the excel file automatically...

Thanks in advance for your help!!

1. Can you Link to Excel and create the Pivot Tables in Access?
2. Have you tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel?
3. Are you using Excel exclusively for its Pivot Table functionality?

[Suzette]

1. Can you Link to Excel and create the Pivot Tables in Access?
2. Have you tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel?
3. Are you using Excel exclusively for its Pivot Table functionality?

Thanks for your reply, I really appreciate it!

1) I tried to build the report and pivot tables in access first, but was not able to get it to where I need it - creating multiple pivot tables that have different fields and are not connected, putting them in one place, and then summing amounts from them. I also need to manually input one cell record daily.

2) I have not tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel but would like to - could you direct me to where I can get info on how?

3) I am using excel not only to create the different pivot tables, but to also manually update that one cell amount into the spreadsheet daily, and then get other sums of various amounts.

I made a temporary fix by making a desktop shortcut to the macro that would automatically close following the update, so the user can double click on that, wait a second for it to finish, and then open up the excel file...but otherwise I'm out of ideas

Thanks again for your help!!

[ADezii]

Thanks for your reply, I really appreciate it!

1) I tried to build the report and pivot tables in access first, but was not able to get it to where I need it - creating multiple pivot tables that have different fields and are not connected, putting them in one place, and then summing amounts from them. I also need to manually input one cell record daily.

2) I have not tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel but would like to - could you direct me to where I can get info on how?

3) I am using excel not only to create the different pivot tables, but to also manually update that one cell amount into the spreadsheet daily, and then get other sums of various amounts.

I made a temporary fix by making a desktop shortcut to the macro that would automatically close following the update, so the user can double click on that, wait a second for it to finish, and then open up the excel file...but otherwise I'm out of ideas

Thanks again for your help!!

To Run Access Macros from within Excel:

1. First and foremost, you must set a Reference to the Microsoft Access 11.0 Object Library (Access 2003).
2. Declare an Object Variable as type Access.Application in the Declarations Section of a Worksheet Module NOT within the Click() Event of the Command Button containing the primary code. This is because the Object Variable will immediately go out of Scope as soon as the Click() Event ends and you'll never see the Instance of Access being launched.
   

   Dim objAccessAuto As Access.Application

3. In the Click() Event of a Command Button on the same Sheet where you made the Object Variable Declaration, place the following code:
   

   Private Sub CommandButton1_Click()
    
   'Specifically an Access 2003 Application
   Set objAccessAuto = CreateObject("Access.Application.11")
    
   objAccessAuto.Visible = True
    
   '(True Argument) - Open the Database Exclusively - probably a good idea
   objAccessAuto.OpenCurrentDatabase "C:\Test\Test.mdb", True
    
   objAccessAuto.DoCmd.RunMacro "mcrTest"
    
   End Sub

4. C:\Test\Test.mdb will be opened in an Access Window, and mcrTest will be run. In this particular case it simply executes a Query.
5. OpenCurrentDatabase() is specifically designed to work within the OLE Automation context - you can reference it through the Help Files.