Access to run Excel Macros

[Mykal]

I have access opening a excel document and running one macro. I leave the excel document open so I can run another macro later. When the time comes to activate excel and run macro 2, I have a problem getting the second macro to run. I can get excel window activated. I thank you in advance for any assistance provided.

Thanks.

 

[bat17]

what happens when you try to run the maco? do you still have a reverence to the excel file in code?

Peter

 

[Mykal]

When I get to the second macro I see the excel window come to the front and everything just stops. When I go back to access, the error in the message box says:

Run-time error'91':
Object variable or with block variable not set

Thank you.

 

[bat17]

Can you post your code? It could be that the variable for Xl is going out of scope between sessions.

Peter

 

[Mykal]

Here is basically the code that I am using right now.

Private Sub XMacro1()
Dim ExcelApp As Object
Dim fNameAndPath As String

fNameAndPath = "\\comp1\Planning\Database\Macro.xls"
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.WorkBooks.Open (fNameAndPath)
ExcelApp.Visible = True
vardirpath = Me.DirPath

WordApp.Run "Macro1", vardirpath


End Sub

Private Sub XMacro2()
Dim ExcelApp As Object

VarTI1 = DFirst("[TI]", "List")
ExcelApp.Activate "Macro2", VarTI1
End Sub

Thank you for you time and assistance with this.

 

[Norie]

In the second macro you have not referenced Excel.

Do you need to keep Excel open after running the first macro?

If not this might work:

Private Sub XMacro1()
Dim ExcelApp As Object
Dim fNameAndPath As String

    fNameAndPath = "\\comp1\Planning\Database\Macro.xls"
    
    Set ExcelApp = CreateObject("Excel.Application")
    
    ExcelApp.Workbooks.Open (fNameAndPath)
    
    ExcelApp.Visible = True
    
    vardirpath = Me.DirPath
    
    ExcelApp.Run "Macro1", vardirpath
    
    ExcelApp.Close

End Sub

Private Sub XMacro2()
Dim ExcelApp As Object

    fNameAndPath = "\\comp1\Planning\Database\Macro.xls"
    
    Set ExcelApp = CreateObject("Excel.Application")
    
    ExcelApp.Workbooks.Open (fNameAndPath)
    
    ExcelApp.Visible = True
    
    VarTI1 = DFirst("[TI]", "List")

    ExcelApp.Activate "Macro2", VarTI1
    
    ExcelApp.Close
    
End Sub

BTW what do the macros do? Is it not something that could be done in Access?

 

[bat17]

I think that you just need to move your ExcelApp to the Declaration Section at the top of the module. I would also set it to nothing at the end of the second macro to free up the memory again. try:-

Dim ExcelApp As Object 

Private Sub XMacro1() 
Dim fNameAndPath As String 
fNameAndPath = "\\comp1\Planning\Database\Macro.xls" 
Set ExcelApp = CreateObject("Excel.Application") 
ExcelApp.WorkBooks.Open (fNameAndPath) 
ExcelApp.Visible = True 
vardirpath = Me.DirPath 

WordApp.Run "Macro1", vardirpath 


End Sub 

Private Sub XMacro2() 
VarTI1 = DFirst("[TI]", "List") 
ExcelApp.Activate "Macro2", VarTI1 
set ExcelApp = nothing
End Sub

HTH

Peter

 

[Mykal]

Thank you that's what I needed. Once again thank you everyone for you assistance.