Access to Excel back to Access

[Jonbob]

I have code that Runs Macros is Access, then opens up Excel, then goes back to the same Access Database to run more macros. Everything works fine, except when it goes back to access, it opens up a new Access window and calls the same database again. How do I make it work so it does back to the Access window already open? I believe the problem is with my Excel code- see below:

Sub open_access2()
Dim DB As Object
Dim pth As String

pth = ActiveWorkbook.Path & "\Jewelry Labels.mdb"
Set DB = CreateObject("Access.Application")

DB.Visible = False
DB.OpenCurrentDatabase (pth)
DB.Docmd.RunMacro "Print_Labels" ' Macro name in DataBase

Set DB = Nothing

End Sub

 

[Norie]

Take a look at the GetObject function instead of CreateObject.

Can you explain what you are actually trying to do? There could be another way to do it.

 

[Jonbob]

I have to pull information from a SQL Database in Access, manipulate in Excel, and then print out labels in Access.

 

[Norie]

What manipulation is being done in Excel?

Can it not be done in Access?

 

[Jonbob]

I figured it out.. I changed my Access code..see below...

Agreed- there probably was a easier way- like doing this all in Access, but I was too far into the project to turn back.

Option Compare Database

Function Run_Labels()

Dim obj_xl As Object
Dim x As Variant

' Set Excel information

Set obj_xl = CreateObject("Excel.Application")
With obj_xl.Application
.Visible = True

' Open the Workbook

.Workbooks.Open "G:\Everyone\Jewelry Labels.xls"

'Run macro

x = .Run("run_labels_excel")
.Quit

End With

DoCmd.RunMacro "Print_Labels"

End Function