Run a Saved Export in Access From Excel VBA

[carpy1985]

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

Cheers!

 

[xenou]

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):

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).

 

[carpy1985]

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]

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):

[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]