Create MS-Access VBA module from MS-Excel

Question

I am using a large Excel *.xlsx table to create a MS Access *.accdb Database, including some queries. This provides a final *.accdb file with functionalities to serve the user with a maximum convenience.

Therefore I would like to provide some VBA code in a module, running multiple queries after another.

Long story short: How can I create a Module FROM a VBA script in a MS-Excel *.xlsx file and append it to a MS-Access *.accdb file via VBA?

I found this related post From MS Excel VBA, create a Standard Module in MS Access

This lead me to the solution how to create a blank module in my destination file

'REQUIRES: MS Access 12.0 Object Library 
Dim ObjAccess As Access.Application 
Dim strPath As String 
strPath = "C:\Temp\MyDB_DEV_1.MDB" 
Set ObjAccess = New Access.Application 
With ObjAccess 
    .OpenCurrentDatabase strPath, True 
    .DoCmd.RunCommand acCmdNewObjectModule 
    .DoCmd.Save acModule, "Module1" 
    .DoCmd.Rename "MyCodedModule", acModule, "Module1" 
    .CloseCurrentDatabase 
   .Quit 
End With 
Set ObjAccess = Nothing

How can I now retreive this blank module "MyCodedModule" from this file, to add lines of code like

 Dim m as DAO.Module
 Set m = GetModuleFromDataBase("MyCodedModule") 'HOW?
 m.AddFromString "Sub foo()" & vbCrLf & _
                 "  Debug.Print ""FOO""" & vbCrLf & _
                 "End Sub"

HeXor HeXor · Accepted Answer · 2017-05-18T12:12:32

So I found the solution, sharing it here in case someone needs this aswell

The following codes applies these steps

Create a new default module
rename the new module
add code to the new module

And the code, which has been used in MS Excel looks like:

'REQUIRES: MS Access <version> Object Library 

Dim dbpath            As String              'Path of the database to add the module to
Dim moduleName        As String              'Name of the new module to be created in the database
Dim strCode           As String              'String for the code to be added to new module
Dim defaultModuleName As String              'Default module name chosen by Access
Dim ObjAccess         As Access.Application  'Access Application Object

'Set database path
dbpath = "C:\PATH\To\MY\database.accdb"

'Define Code String with line breaks (vbCrLf)
strCode = "Sub foo()" & vbCrLf & _
          "  Debug.Print ""FOO""" & vbCrLf & _
          "End Sub"

'(English 'Module1', German 'Modul1', ...)
defaultModuleName = "Module1"

'Set new module name
moduleName = "MyModule"

'Initialize Access Application Object
Set ObjAccess = New Access.Application

'Open Database and add blank default module, rename afterwards
ObjAccess.OpenCurrentDatabase dbpath, True
ObjAccess.DoCmd.RunCommand acCmdNewObjectModule
ObjAccess.DoCmd.Save acModule, defaultModuleName
ObjAccess.DoCmd.Rename moduleName, acModule, defaultModuleName
ObjAccess.DoCmd.Save acModule, moduleName

'Loop Modules in Database
For i = 0 To ObjAccess.Modules.Count - 1
    'Find module name
    If ObjAccess.Modules(i).Name = moduleName Then
         'add code to module
         ObjAccess.Modules(i).AddFromString strCode
         Exit For
    End If
Next

'Save and Close
ObjAccess.DoCmd.Save acModule, moduleName 
ObjAccess.CloseCurrentDatabase
ObjAccess.Quit

Create MS-Access VBA module from MS-Excel

1 Answers