N/APosted on - 09/22/2011
I have an Excel spreadsheet which gets some of its information from an Access database. I have written several macros in this spreadsheet using Visual Basic code. I find that gives me more flexibility than just recording my keystrokes. I would somehow like to create a macro in this spreadsheet which would open the Access database and, if possible, run a macro contained in the database.
Since all of my Visual Basic knowledge is self taught, I am not familiar with all of the code. What code should I use to switch from one program to another?
Using an Excel Macro to open an Access database.
Open Microsoft Excel and click alt+11 key combination on your keyboard.
Right click on sheet 1 and choose insert then module. Copy and paste the following code in the space that will appear
Global oApp As Object
Dim LPath As String
Dim LCategoryID As Long
'Path to Access database
LPath = "Include here the directory where your access file is located"
'Open Access and make visible
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
'Open Access database as defined by LPath variable
'Open form called Categories filtering by CategoryID
LCategoryID = Range("A2").Value
oApp.DoCmd.OpenForm "Categories", , , "CategoryID = " & LCategoryID
Edit the code accordingly especially the path to your document. Thereafter press control+s to save it. Provide a name for the module and save to a place of your choice.
Hope this helps.