Code to open and close Excel not working (1 Viewer)

[cpremo]

I use the following code to open excel, run a macro and then close excel from an access database. Unfortunately, I get the following error when I try re-runing the code (from a loop statement):

Method 'Run' of object '_Application' failed!

It seems that the second time through it isn't opening the "Protocols-Cleanup.xls" file and therefore can't run the "GetFromAccess" macro. Any suggestions????

Code used by Access
**************************************************
Dim ObjExcel As Excel.Application
Set ObjExcel = New Excel.Application
ObjExcel.Visible = True
AppActivate "Microsoft Excel"
Workbooks.Open (strPath & "\Protocols-Cleanup.xls")

'The "strPath" statement passes the database location from the MainMenu
'Further, this "Run" statement moved the "Run Protocols_Filters",
'"Run Builtin_Filters", and "Run Filters_Filters" to the excel form
ObjExcel.Run "GetFromAccess", strPath
Workbooks.Close
ObjExcel.Quit
ObjExcel.Application.Quit
Set ObjExcel = Nothing

 

[DJkarl]

I use the following code to open excel, run a macro and then close excel from an access database. Unfortunately, I get the following error when I try re-runing the code (from a loop statement):

Method 'Run' of object '_Application' failed!

It seems that the second time through it isn't opening the "Protocols-Cleanup.xls" file and therefore can't run the "GetFromAccess" macro. Any suggestions????

Code used by Access
**************************************************
Dim ObjExcel As Excel.Application
Set ObjExcel = New Excel.Application
ObjExcel.Visible = True
AppActivate "Microsoft Excel"
Workbooks.Open (strPath & "\Protocols-Cleanup.xls")

'The "strPath" statement passes the database location from the MainMenu
'Further, this "Run" statement moved the "Run Protocols_Filters",
'"Run Builtin_Filters", and "Run Filters_Filters" to the excel form
ObjExcel.Run "GetFromAccess", strPath
Workbooks.Close
ObjExcel.Quit
ObjExcel.Application.Quit
Set ObjExcel = Nothing

I suspect the line I've highlighted in red is the issue, the Workbooks method is part of the Excel Application so to call it you should need to put

ObjExcel.Workbooks.Open (strPath & "\Protocols-Cleanup.xls")

Also I don't think you really need the AppActivate method, all that does is set the focus to Excel, which if you want to see what it's doing I guess it's fine, but otherwise it really isn't needed based on the code I'm seeing here.

 

[cpremo]

After a little more tweeking, I've been able to get the code to run for multiple loops with out the error showing. However, when I look at the Task Manager, the Excel.EXE is still active even though I've told it to close. Or at least I thought.

 

[boblarson]

After a little more tweeking, I've been able to get the code to run for multiple loops with out the error showing. However, when I look at the Task Manager, the Excel.EXE is still active even though I've told it to close. Or at least I thought.

Make sure you use EXPLICIT references to objects and not things like

Workbooks.Close

instead use

ObjExcel.ActiveWorkbook.Close

if you don't use explicit references using the objects you have declared, then Access will hold Excel open until you close Access because you have an inferred reference to an object that hasn't been declared and therefore can't be manipulated. And, unless the objects are all closed Excel won't release.

 

[cpremo]

That was it. Thanks for the help!!

 

[boblarson]

 

[irfanali]

I needed a code wherein on a click of a button, access opens a specified excel file, (copies a segement of data from the Excel file and save it at another location as a new file) and then set focus back to Access

The line in italics is taken care by Excel macros.

I tried the Code above with the changes..

The following is the Code :

Dim ObjExcel As Excel.Application
Set ObjExcel = New Excel.Application
ObjExcel.Visible = True
ObjExcel.Workbooks.Open ("C:\Documents and Settings\ifa1kor\Desktop\Internal_Uploader.xls")
ObjExcel.Run "Macros_Open" ' The Macro is run but it gets stuck here
ObjExcel.ActiveWorkbook.Close
ObjExcel.Quit
ObjExcel.Application.Quit
Set ObjExcel = Nothing

The Funniest part is that as per the Code, Excel opens and executes the Macros and i get the desired result but after delivering the result, the code "ObjExcel.Run "Macros_Open"" gives "Method 'Run' of object '_Application' failed!" error..

Can anyone help clear this confusion ?

Thanks in advance !

 

[cpremo]

If you look at my original code, I included a strpath statement to the code

ObjExcel.Run "GetFromAccess", strPath

Your code doesn't include a path. Try adding

"C:\Documents and Settings\ifa1kor\Desktop"

to your code

ObjExcel.Run "Macros_Open", "C:\Documents and Settings\ifa1kor\Desktop"

 

[irfanali]

I needed a code wherein on a click of a button, access opens a specified excel file, (copies a segement of data from the Excel file and save it at another location as a new file) and then set focus back to Access

The line in italics is taken care by Excel macros.

I tried the Code above with the changes..

The following is the Code :

Dim ObjExcel As Excel.Application
Set ObjExcel = New Excel.Application
ObjExcel.Visible = True
ObjExcel.Workbooks.Open ("C:\Documents and Settings\ifa1kor\Desktop\Internal_Uploader.xls")
ObjExcel.Run "Macros_Open" ' The Macro is run but it gets stuck here
ObjExcel.ActiveWorkbook.Close
ObjExcel.Quit
ObjExcel.Application.Quit
Set ObjExcel = Nothing

The Funniest part is that as per the Code, Excel opens and executes the Macros and i get the desired result but after delivering the result, the code "ObjExcel.Run "Macros_Open"" gives "Method 'Run' of object '_Application' failed!" error..

Can anyone help clear this confusion ?

Thanks in advance !

I finally got the Code to work.. The reason occured to me when i was having my shower this morning...i was like..i m asking my code in Access to close the Workbook and also, i m asking Excel macro to close the workbook as well...of course there will be a conflict in argument !

Came to office, tried it and ting ding di ding...it works...

cpremo Thanks a ton fr the speedy reply !

Let Buddha be with us !