473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Generating a spreadsheet from Access Data with a macro built in

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
4 1463
ADezii
8,834 Expert 8TB
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
2,322 Expert Mod 2GB
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
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
8,834 Expert 8TB
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

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

Similar topics

8
by: Colleyville Alan | last post by:
I have been working on an Access app that takes info from a file and writes it to a spreadsheet on a form, simultaneously saving the spreadsheet to Excel. I got the idea that the same concept...
13
by: Peter L Reader | last post by:
I have to say, from a practical standpoint I'm not all that impressed with the security built into Office 2K3. I'm a small-scale developer building Access apps for a few non-profits locally; I...
0
by: Rich Wallace | last post by:
Hello all, Looking for suggestions and tips if possible. I have an application running on a file server that utilizes the FileSystemWatcher to trap when any Excel files are saved by a user. I...
3
by: Brooke | last post by:
I am new to ASP.NET, but have been programming for about 14 years (C# about 2 years). My manager asked me to develop a web application that would allow employees to view a spreadsheet that is used...
3
by: Eddy | last post by:
I would like to export the output of 2 queries to excel namely: 1. QryProrationbyWBS_1 2. QryProrationbyWBS_2 However I want to do this using the same spreadsheet say sheet1 and sheet2 or...
0
by: dotnetnoob | last post by:
i need to read the data in a spreadsheet and use the data for attribute to put into xml file. i search the web but all the example that i see is to create macro by VBA. can someone give me an...
1
by: reach2prakash | last post by:
I can import the EXCEL spreadsheet using the macro in ms access but the table is not indexed with the Autonumber as we get in the manual import operation. Please assist me to get the indexed data...
1
by: mforema | last post by:
Hi Everybody, I have an Access table with a field filled with outdated hyperlinks. I also have an excel spreadsheet that is the master list for these hyperlinks. The spreadsheet will be updated...
2
by: aston martin | last post by:
Hello, I am new to this forum and new to Visual Basic. I need someone to point me in the right direction. I have been asked to write a spreadsheet (which I have done) and then use a macro which...
0
by: Bob Alston | last post by:
It appears that when Access opens the link to an Excel spreadsheet, it uses an exclusive lock. Understandable if you want updatability. However, I have a client with need for Excel data in a read...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.