Run export from command line

coderGoneOffRoad commented 3 years ago

Is it possible to run export somehow from command line? It would be super useful to be able to automate the export. This is very useful add-in for me, but my .mdb file is not normally handled by access. Instead I have special purpose application that uses .mdb files as it's data save format. I'd do automation on exporting the source and pushing to repo with just click.

This tool is GREAT! Thank you.

joyfullservice commented 3 years ago

Glad to hear you are enjoying it!

Yes, you can launch the export from the command line (i.e. batch file) by doing the following:

Add a public function in a standard module to launch the export from the add-in. I have just put together an example function that will be included in future releases that demonstrates how you can load the add-in at application level and export the current database. Here is a copy of the example:

'---------------------------------------------------------------------------------------
' Procedure : ExampleLoadAddInAndRunExport
' Author    : Adam Waller
' Date      : 11/13/2020
' Purpose   : This function can be copied to a local database and triggered with a
'           : command line argument or other automation technique to load the VCS
'           : add-in file and initiate an export.
'           : NOTE: This expects the add-in to be installed in the default location
'           : and using the default file name.
'---------------------------------------------------------------------------------------
'
Public Function ExampleLoadAddInAndRunExport()

    Dim strAddInPath As String
    Dim proj As Object      ' VBProject
    Dim objAddIn As Object  ' VBProject

    ' Build default add-in path
    strAddInPath = Environ$("AppData") & "\Microsoft\AddIns\Version Control.accda"

    ' See if add-in project is already loaded.
    For Each proj In VBE.VBProjects
        If StrComp(proj.FileName, strAddInPath, vbTextCompare) = 0 Then
            Set objAddIn = proj
        End If
    Next proj

    ' If not loaded, then attempt to load the add-in.
    If objAddIn Is Nothing Then

        ' The following lines will load the add-in at the application level,
        ' but will not actually call the function. Ignore the error of function not found.
        ' https://stackoverflow.com/questions/62270088/how-can-i-launch-an-access-add-in-not-com-add-in-from-vba-code
        On Error Resume Next
        Application.Run strAddInPath & "!DummyFunction"
        On Error GoTo 0

        ' See if it is loaded now...
        For Each proj In VBE.VBProjects
            If StrComp(proj.FileName, strAddInPath, vbTextCompare) = 0 Then
                Set objAddIn = proj
            End If
        Next proj
    End If

    If objAddIn Is Nothing Then
        MsgBox "Unable to load Version Control add-in. Please ensure that it has been installed" & vbCrLf & _
            "and is functioning correctly. (It should be available in the Add-ins menu.)", vbExclamation
    Else
        ' Set the active VB project so we can call the export function.
        Set VBE.ActiveVBProject = objAddIn

        ' Launch export for current database.
        ' (It is very important to use RunExportForCurrentDB when calling from the database from
        '  which you want to export source.)
        Application.Run "RunExportForCurrentDB"
    End If

End Function

Then, the second part is to launch your database from the command line and run a macro. This can be done using the /x or /cmd command line arguments. (You will need to have a Macro call your VBA function.)

A third thing you might want to do is to add a VBA sub that closes the Export form, and perhaps your database file, depending on exactly what you are wanting to do in your automation process. This can be specified in the Run Sub After Export option for your project. (I.e. DoCmd.Close acForm ,Forms(0).Name to close an open form.)

Hope that helps get you started!! 😄

Indigo744 commented 3 years ago

Hello,

I'm sorry to dig up this closed thread, but just to let you know: in order to make this code work, I had to do:

Application.Run "MSAccessVCS.RunExportForCurrentDB"

Otherwise I get "Unknown function RunExportForCurrentDB"...

joyfullservice commented 3 years ago

Hello,

I'm sorry to dig up this closed thread, but just to let you know: in order to make this code work, I had to do:
Application.Run "MSAccessVCS.RunExportForCurrentDB"
Otherwise I get "Unknown function RunExportForCurrentDB"...

Interesting... I was thinking it wouldn't need that because I had set the ActiveVBProject, but that may be because I was testing from the immediate window. I will go ahead and add the fully qualified name.

Thanks @Indigo744 for pointing this out!

jhgarrison commented 2 years ago

Also needs an update to strAddInPath. Instead of

strAddInPath = Environ$("AppData") & "\Microsoft\AddIns\Version Control.accda"

it's now

strAddInPath = Environ$("AppData") & "\MSAccessVCS\Version Control.accda"

Oh.... I see it's correct in the codebase. You might want to update the code in the comment above as well, or just replace it with a link to the source file :-)

Thanks for an awesome system.

joyfullservice / msaccess-vcs-addin

Run export from command line #88