By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Generating a spreadsheet from Access Data with a macro built in

P: 29
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?
Jan 18 '10 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. 'First, set a Reference to the Microsoft Excel XX.X Object Library
  2. Dim XLApp As Object
  3.  
  4. '****************** Customize to your liking ******************
  5. Const conPATH_TO_EXCEL_FILE As String = "C:\Stuff\Macro.xls"
  6. Const conMACRO_NAME As String = "Macro1"
  7. '**************************************************************
  8.  
  9. 'Open the XLS file and make it Visible
  10. Set XLApp = CreateObject("Excel.Application")
  11.  
  12. With XLApp
  13.   .Application.Visible = True
  14.   .UserControl = True
  15.   .Workbooks.Open conPATH_TO_EXCEL_FILE
  16.   .Application.Run conMACRO_NAME
  17.   .ActiveWorkbook.Save
  18. End With
  19.  
  20. XLApp.Quit
Jan 19 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportToExcel()
  2.  
  3.     Dim myExcel As New Excel.Application
  4.     Dim myWB As Excel.Workbook
  5.     Dim mySheet As New Excel.Worksheet
  6.  
  7.     Set myWB = myExcel.Workbooks.Add
  8.     Set mySheet = myWB.Sheets(1)
  9.     mySheet.Range("A1") = "Testing"
  10.     myExcel.Visible = True
  11.  
  12.     Dim strModule As String
  13.     strModule = "Option explicit" & vbNewLine
  14.     strModule = strModule & "Public sub Testing()" & vbNewLine
  15.     strModule = strModule & "Msgbox 'Test'" & vbNewLine
  16.     strModule = strModule & "End Sub"
  17.     Debug.Print mySheet.CodeName
  18.     Dim myMod As Excel.Module
  19.     Set myMod = myWB.Modules.Add
  20.     myMod.Activate
  21.     myMod.Visible = xlSheetVisible
  22.     myMod.Name = "modVBA_Access"
  23.  
  24.     'myMod=strModule <<-- Didn't work
  25.  
  26.  
  27.     Set mySheet = Nothing
  28.     Set myWB = Nothing
  29.     Set myExcel = Nothing
  30.  
  31.  
  32. End Sub
Jan 19 '10 #3

P: 29
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.
Jan 19 '10 #4

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim VBProj As VBIDE.VBProject
  2. Dim vbComp As VBIDE.VBComponent
  3. Dim CodeMod As VBIDE.CodeModule
  4. Dim lngLineNum As Long
  5. Const conDBL_QUOTE As String = """"
  6.  
  7. Set VBProj = ActiveWorkbook.VBProject
  8. Set vbComp = VBProj.VBComponents("Module1")
  9. Set CodeMod = vbComp.CodeModule
  10.  
  11. With CodeMod
  12.   lngLineNum = .CountOfLines + 1
  13.     .InsertLines lngLineNum, "Public Sub TestSub()"
  14.   lngLineNum = lngLineNum + 1
  15.     .InsertLines lngLineNum, "Msgbox " & conDBL_QUOTE & "Hello World!" & conDBL_QUOTE
  16.   lngLineNum = lngLineNum + 1
  17.     .InsertLines lngLineNum, "End Sub"
  18. End With
Jan 19 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.