This is a discussion on VBA set up - calling Modules within the Microsoft Access forums, part of the Question Forums category; Questions: - Once I name a variable in One module how do I get it to keep it's value when ...
- Once I name a variable in One module how do I get it to keep it's value when calling another module?
- Do I need to keep using Dim in each called module?
- Is there a much better way to do this?
I have the following In a Form Object.:
Whic calls this:Code:Public Sub cmdOpenExcelTemplate_Click() Call MasterReportBuilderAc End Sub
In a Module.Code:Public Sub MasterReportBuilderAc() Dim strFullPath As String Dim sXL_Path As String Dim XLapp As Excel.Application Dim XLwb As Excel.Workbook Dim ReportName As String Dim Objcat As New ADOX.Catalog Dim oConn As New ADODB.Connection ' Get the Path Name strFullPath = CurrentDb().Name sXL_Path = Left(strFullPath, InStrRev(strFullPath, "\")) ReportName = "NewReport" MsgBox (sXL_Path) Set XLapp = CreateObject("excel.application") Set XLwb = Workbooks.Open(sXL_Path & TargetXLFile) XLapp.Visible = True XLwb.SaveAs (sXL_Path & ReportName) Call CountyTIVac Call DistToCoastAc Call LimitProfileAc ' See Module below XLapp.Quit Set Objcat = Nothing Set oConn = Nothing Set XLapp = Nothing Set XLwb = Nothing End Sub
Next I have all the modules being called (there will be more than 3) each in their own module:
Code:Sub DistToCoastAc() Dim sDB_Path As String Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim Rw As Long Dim fld As ADODB.Field Dim ShDest As Worksheet Dim Cmd As ADODB.Command Dim VtSQL As String Dim XLwb As Excel.Workbook Dim sXL_Path As String Dim ReportName As String sDB_Path = CurrentDb().Name 'SET UP Set cnn = CurrentProject.Connection Set Cmd = New ADODB.Command Set Cmd.ActiveConnection = cnn 'CREATE AND RUN QUERY VtSQL = "" VtSQL = VtSQL & "SELECT DistCoast_Complete.CountOfLOCID, DistCoast_Complete.SumOfTIVVALUE FROM DistCoast_Complete;" Cmd.CommandText = VtSQL ' Cmd.CommandType = adCmdQuery Cmd.Execute ' PUT QUERY TO RECORD SET Set rst = New ADODB.Recordset Set rst.ActiveConnection = cnn rst.Open Cmd ' CREATE LINK TO EXCEL FILE & DO STUFF IN EXCEL Set XLwb = Workbooks(sXL_Path & ReportName) With XLwb.Worksheets("CountyTIV") .Range("A7").CopyFromRecordset rst End With Rw = XLwb.Worksheets("CountyTIV").Range("A7").End(xlDown).Row With XLwb.Worksheets("CountyTIV") .Range("A" & Rw + 1, "A999").EntireRow.Delete End With ' Close the connection Set cnn = Nothing Set Cmd = Nothing End Sub
Declare a Public Variable. Then you can set its value in one Module, and use that value in another. If you look at the built-in VBA help on "Declaring Variables", it should provide help on how to does this, if you need it.
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"