Run a Saved Export in Access From Excel VBA

carpy1985

Board Regular
Joined
Nov 16, 2011
Messages
79
Hi Guys,

Looking to speed up my daily tasks and would like to run a saved export using VBA in Excel.

New to working with Access in Excel VBA and i have seen this alot...

DoCmd.OpenStoredProcedure ("Export-XXXXX")

...but cant get my head round how im supposed to use it or if there is a better way!

Any help is appreciated :cool:

Cheers!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
I typically use this kind of code (there are other options but I've found it's simple and effective).
This is an example of using a docmd type command, as well as just running a procedure in a standard module (the procedure can then do anything you want):

Code:
Sub Foo()
    
    [COLOR="#008000"]'Open DB and run procedure
[/COLOR][COLOR="#000080"]    Set[/COLOR] objAccess = CreateObject("Access.Application")
    [COLOR="#000080"]If[/COLOR] Not objAccess [COLOR="#000080"]Is Nothing Then
[/COLOR]        [COLOR="#000080"]With[/COLOR] objAccess
            .OpenCurrentDatabase "C:\Folder1\db1.mdb", False
	    .DoCmd.SetWarnings [COLOR="#000080"]False
[/COLOR]	    .DoCmd.RunSQL "UPDATE This SET That;" [COLOR="#008000"]'//Run an action Query[/COLOR]
	    .DoCmd.SetWarnings [COLOR="#000080"]True[/COLOR]
	    .Run("Macro1") [COLOR="#008000"]'//Run a macro that does anything you need it to do[/COLOR]
        [COLOR="#000080"]End With[/COLOR]
    [COLOR="#000080"]End If[/COLOR]

    [COLOR="#008000"]'-----------------------------------------------------------------------[/COLOR]
    'Close Access
    [COLOR="#000080"]If Not[/COLOR] objAccess [COLOR="#000080"]Is Nothing Then[/COLOR]
        [COLOR="#000080"]With[/COLOR] objAccess
            .CloseCurrentDatabase
            .Quit
        [COLOR="#000080"]End With
    End If

End Sub[/COLOR]

I can't remember what happens with security warnings but I have most (or all) of my databases in a trusted location (or set with low macro security if using Access 2003) so I never see that message about this database contain potentially harmful code blah blah blah).
 
Last edited:

carpy1985

Board Regular
Joined
Nov 16, 2011
Messages
79
Not only did your code solve my problem - it also helped me to better understand how to interact with Access from Excel VBA!

Cannot thank you enough!

(That code is being saved!)
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
No problem. I should note that once code is tested it's a good idea to put a precautionary error handler in the routine to make sure that no matter what else happens you close Access. I tend to use a return value also for such calls, so I can get a flag value back telling me if the procedure succeeded or failed (i.e. true/false or 0/1 return values):

Code:
[COLOR="Navy"]Function[/COLOR] Foo() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:

    [COLOR="SeaGreen"]'Open DB and run procedure[/COLOR]
    [COLOR="Navy"]Set[/COLOR] objAccess = CreateObject("Access.Application")
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] objAccess
        .OpenCurrentDatabase "C:\Folder1\db1.mdb", False
        .Run "Macro1"
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    Foo = True [COLOR="SeaGreen"]'//SUCCESSFUL EXIT[/COLOR]

ErrHandler:
[COLOR="SeaGreen"]'//Make sure we close Access when finished[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] objAccess
        .CloseCurrentDatabase
        .Quit
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] TestIt()
    
    [COLOR="Navy"]If[/COLOR] Foo() [COLOR="Navy"]Then[/COLOR]
        [COLOR="SeaGreen"]'//Our Procedure Succeeded[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="SeaGreen"]'//Oops - something went wrong[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top