Export Multi Mods to Excel

[nat1967]

Hi group,

I currently have 3 modules I use to transfer data to excel. Each works fine on its own. However, I am trying to find a way to call all 3 mods and transfer all data to excel.

Example of one module:

*********************
Sub EncoreExcel()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intLastCol As Integer

Const conMAX_ROWS = 200
Const conSHT_NAME = "ENCORE" 'excel sheet name
Const conWKB_NAME = "c:\temp\releasestatus.xls" 'excel file location and name

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("ExcelMergeENCORE", dbOpenSnapshot) 'name of query or table to export

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Cells(2, 3) = Date
.Range(.Cells(11, 1), .Cells(rs.RecordCount, intLastCol)).Font.Bold = True
.Range(.Cells(11, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range("A11&quot.CopyFromRecordset rs 'location the paste to excel starts
End With
End With

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

End Sub
*************************

All 3 modules are formated the same way. query or records being called, worksheets, and placement on worksheet change in each mod but the overall format of the mod is the same.

Since the first module "opens" excel and the workbook, each subsequent module trys to do the same thing but the workbook is already opened.

So, i thought to myself, just remove the reference to opening the workbook....WRONG!!! LOL. errooorrrr.....

i have tried several combos and pulled out the 'ol Access Dev Handbook. cant find a solution.

What I would like to end up with is a sub behind a command button that calls the mods>

call mod1
call mod2
call mod3

All data is transfered and the users dont have to execute 3 different mods individually.

Does any have any thoughts on how to set this code up right?

TIA Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Whew!

Got it!!!

Set objWkb = .ActiveWorkbook


Have A Great Day!!!,

Nathan
Senior Test Lead