Generating a spreadsheet from Access Data with a macro built in

[CoreyReynolds]

Hello,

I currently have a VBA that vomits a bunch of data into a neat little Excel spreadsheet from my Access 2007 database app to distribute to my managers.

There's a couple macros that would be handy to have in this spreadsheet for them to use... is it possible programatically to insert an Excel Macro into the Excel spreadsheet that I am creating in VBA in access?

[ADezii]

I am a little fuzzy on your question, but are you asking how to Run an Excel Macro from within Access, via VBA? If it is, then this can be done via Automation Code, as in:

'First, set a Reference to the Microsoft Excel XX.X Object Library
Dim XLApp As Object

'****************** Customize to your liking ******************
Const conPATH_TO_EXCEL_FILE As String = "C:\Stuff\Macro.xls"
Const conMACRO_NAME As String = "Macro1"
'**************************************************************

'Open the XLS file and make it Visible
Set XLApp = CreateObject("Excel.Application")

With XLApp
  .Application.Visible = True
  .UserControl = True
  .Workbooks.Open conPATH_TO_EXCEL_FILE
  .Application.Run conMACRO_NAME
  .ActiveWorkbook.Save
End With

XLApp.Quit

[TheSmileyCoder]

I've managed to open a excel workbook, add and name a module, but I can't seem to find the method to start typing to it.

Public Sub ExportToExcel()

    Dim myExcel As New Excel.Application
    Dim myWB As Excel.Workbook
    Dim mySheet As New Excel.Worksheet
    
    Set myWB = myExcel.Workbooks.Add
    Set mySheet = myWB.Sheets(1)
    mySheet.Range("A1") = "Testing"
    myExcel.Visible = True
    
    Dim strModule As String
    strModule = "Option explicit" & vbNewLine
    strModule = strModule & "Public sub Testing()" & vbNewLine
    strModule = strModule & "Msgbox 'Test'" & vbNewLine
    strModule = strModule & "End Sub"
    Debug.Print mySheet.CodeName
    Dim myMod As Excel.Module
    Set myMod = myWB.Modules.Add
    myMod.Activate
    myMod.Visible = xlSheetVisible
    myMod.Name = "modVBA_Access"

    'myMod=strModule <<-- Didn't work


    Set mySheet = Nothing
    Set myWB = Nothing
    Set myExcel = Nothing
    

End Sub

[CoreyReynolds]

Thanks TheSmileyOne, that's actually what I am talking about.
You're right though - I'm not sure how exactly to put the code into it. I'll keep looking for that and I'll post back here if I find anything (before anyone else does).


Btw - if this is possible. Wouldn't it be cool to create an accessVBA app that opens an ExcelVBA app that opens an accessVBA app? Tiers of awesome :)!

Well, leaving the country in an hour - but i'll be back in a week.

[ADezii]

Hello TheSmileyOne. Here is how you would write the code in Excel to Add the TestSub() Sub-Procedure at the end of Module1. You will need to set a Reference to the Microsoft Visual Basic Extensibility 5.3 Object Library. Duplicating this functionality from Access, via Automation, should not be that difficult.

Dim VBProj As VBIDE.VBProject
Dim vbComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim lngLineNum As Long
Const conDBL_QUOTE As String = """"

Set VBProj = ActiveWorkbook.VBProject
Set vbComp = VBProj.VBComponents("Module1")
Set CodeMod = vbComp.CodeModule

With CodeMod
  lngLineNum = .CountOfLines + 1
    .InsertLines lngLineNum, "Public Sub TestSub()"
  lngLineNum = lngLineNum + 1
    .InsertLines lngLineNum, "Msgbox " & conDBL_QUOTE & "Hello World!" & conDBL_QUOTE
  lngLineNum = lngLineNum + 1
    .InsertLines lngLineNum, "End Sub"
End With