Add a new module on the fly?

[DRH192]

Hello All,

Is it possible to create a new module and define its contents from an already existing module?

The background to the question is as follows. I am currently automating lots of reports. I have a table in a single database to hold variables (passwords to applications, paths to templates etc)

I want to be able to keep adding to this table as I go through automating the reports, but I want the variables in the table to become available in code as global constants.

I currently have the following code:

Public Sub DefineVariables()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from variables")

Debug.Print "Option Compare Database"
Debug.Print ""
Debug.Print ""


Debug.Print "'GLOBAL VARIABLES"
While Not rs.EOF

Debug.Print "'" & rs!VariableID & ", " & rs!VariableName & ", " & rs!VariableValue

rs.MoveNext
Wend


rs.MoveFirst

Debug.Print ""

While Not rs.EOF

Debug.Print "Global " & rs!VariableName & " As String"

rs.MoveNext
Wend


rs.MoveFirst

Debug.Print ""
Debug.Print "Public Sub SetVariables()"

While Not rs.EOF

Debug.Print rs!VariableName & " = GetVar(" & rs!VariableID & ")"

rs.MoveNext
Wend
Debug.Print "End Sub"


Set rs = Nothing
Set db = Nothing

End Sub

Public Function GetVar(VarID As Integer) As String
GetVar = DLookup("[VariableValue]", "Variables", "[VariableID] = " & VarID)
End Function

Which produces an output like this in the immediate window:

'GLOBAL VARIABLES
'7, ReportsmartID, 2
'8, ReportsmartDomain, Reportsmart
'9, ReportsmartUserName, DHAMILTON
'10, XansaJMSReportsFolder, T:\Information Reporting\Xansa JMS Reporting\B.O. Reports\AutomatedReports\
'11, XansaJMSExcelTemplate, O:\Contract Management Support Team\XANSA\Xansa JMS Daily Report\Xansa JMS Daily Report_Template.xls
'12, CombinedFailsDB, T:\Combined_Fails.mdb
'13, LocalSPUFI_Folder, C:\SPUFI\

Global ReportsmartID As String
Global ReportsmartDomain As String
Global ReportsmartUserName As String
Global XansaJMSReportsFolder As String
Global XansaJMSExcelTemplate As String
Global CombinedFailsDB As String
Global LocalSPUFI_Folder As String

Public Sub SetVariables()
ReportsmartID = GetVar(7)
ReportsmartDomain = GetVar(8)
ReportsmartUserName = GetVar(9)
XansaJMSReportsFolder = GetVar(10)
XansaJMSExcelTemplate = GetVar(11)
CombinedFailsDB = GetVar(12)
LocalSPUFI_Folder = GetVar(13)
End Sub

What I basically want is to have the output put into a new module when the program starts to run.

Hope that makes sense.

Any help much appreciated

 

[JerryKlmns]

There is an undocumented LoadFromText (No help available from F1) method to import a module

LoadFromText acModule, ModuleNameHere , PathToFile & "\ModuleFileNameHere.xcm"

and ofcourse

DoCmd.DeleteObject acModule, ModuleNameHere
to delete one

 

[PWise]

look @ then AddFromString Method