Asked By
susan1962
0 points
N/A
Posted 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?Â
Answered By
alphae
15 points
N/A
#128293
Using an Excel Macro to open an Access database.
HI Susan,
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
Sub OpenAccess()
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
 oApp.OpenCurrentDatabase LPath
 'Open form called Categories filtering by CategoryID
 LCategoryID = Range("A2").Value
oApp.DoCmd.OpenForm "Categories", , , "CategoryID = " & LCategoryID
End Sub
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.
-alphae-
