Using an Excel Macro to open an Access database.

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? 

• Status: Open
• Question Views: 1024
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS Excel

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-

About alphae

Questions
1

Answers
45

Best Answers
15

• Vote Up 0 Vote Down

• Posted on - 10/04/2011
• Question Category: MS Excel