Friday, 17 October 2014

How to Run an Access Macro from Excel VBA Macro

Excel VBA Macro to Run an Access Macro
Suppose we have a MS Access Database .In this Access DB File , we have a Macro called "Clear_Tables" , this Macro is designed in the MS Access file , which will run the some Queries internally designed in MS Access File, as shown below :


Now If  you want to Run the above Macro "Clear_Tables"  in above Access File , you can simply provide the Details of  the Access File , Macro Name in the Excel Sheet from where you are running that Macro, as shown below :



Finally Run the following Macro from Excel , which will Run an Access Macro 

Sub Clean_DB_Files()

Dim SrcDB_Name As String
Dim SrcFolderPath As String
Dim SrcFilExt As String
Dim SourceDB_File 'As String
Dim TargetFolderPath As String
Dim WS As Worksheet
Dim Acc_DB As Object
Dim FSO As Object

Set WS = ThisWorkbook.Sheets("Clean_DB_Files")
Set Acc_DB = CreateObject("Access.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

For X = 2 To 100
If WS.Cells(X, 2) = "" Then Exit For

SrcDB_Name = WS.Cells(X, 1)
SrcFilExt = WS.Cells(X, 2)
DB_Macro = WS.Cells(X, 3)
SrcFolderPath = WS.Cells(X, 4)


    If Right(SrcFolderPath, 1) <> "\" Then
        SrcFolderPath = SrcFolderPath & "\"
    
    End If

'Checking the Folder Path existence

If FSO.FolderExists(SrcFolderPath) = False Then
WS.Cells(X, 5) = "Failed"
MsgBox ("Source Folder Does Not Exist or Path Not Found")
GoTo Nxt:
End If

SourceDB_File = SrcFolderPath & SrcDB_Name & SrcFilExt

'Checking the Source DB File existence in the Path
If FSO.FileExists(SourceDB_File) = False Then
WS.Cells(X, 5) = "Failed"
MsgBox ("Source DB File Does Not Exist or Path Not Found")
GoTo Nxt:
End If

Acc_DB.Visible = False
Acc_DB.OpenCurrentDatabase (SourceDB_File)
Acc_DB.DoCmd.RunMacro DB_Macro
Acc_DB.CloseCurrentDatabase

WS.Cells(X, 5) = "Success"

Nxt:
Next X

End Sub

Note :
In the Excel File shown above you can list out all the MS-Access files , and corresponding existed Macros of each Access file , from which you want to Run the Macros.

Make sure that the Macro which you want to execute should exist in the Source Database File.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts