Opening and closing an excel file

[corinereyes]

Hi All,

I have this code that opens and closes an excel file. But before to close i need to refresh an Ms Query in excel the user inputs the criteria and after that the file closes. However, when i try to open the file again via the code (command button in access) it gives me error --> runtime error 1004, Method of Object Global failed. etc...

Thanks so much in advance for your help.

Public Function OpenExcelFile(strFilePath As String) As Boolean
'Required: Tools > Refences: Add reference to Microsoft Excel Object Library
'On Error Resume Next

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open(strFilePath)
appExcel.Visible = True

'Do Something or Just Leave Open

Worksheets("Tmpt").Select
Sheets("tmpt").QueryTables(1).Refresh BackgroundQuery:=False
Workbooks("Finalpay.xls").Close SaveChanges:=False



Set appExcel = Nothing
Set myWorkbook = Nothing

End Function

 

[vba_php]

are you sure the error isn't happening because you're not doing this before you destroy the excel instance in memory?

appExcel.quit

that might only apply though if you use the alternative:

dim xl as excel.application
set xl = new excel.application

just a thought. also, why are you opening and closing the thing 2 times in a row? that seems quite strange, and it seems like there would never be a need to do such thing.

if that doesn't work, you might want to run the "refresh microsoft query" operation from your code, IN EXCEL, using the macro recorder to see what code it shows you.[/code]

 

[The_Doc_Man]

Very often when you see the 1004 error from an external app object it is because it is giving you a generic "I don't know what to do" error with the implication that something is internally hosed up. The last time I worked with Excel and got this error, it was because a particular object pointer had not been properly dereferenced (set to Nothing) so when I went to use it again later it was pointing to something from a prior round of instantiation. I.e. it was pointing to something inside an app object for which the app itself was now dereferenced but the internal object wasn't. Therefore they were out of sync. I can't see what else is going on here, but that is what happened with me.

Adam's suggestion to first make the app quit isn't bad. The other comment, to not close the app if you are going to use it again, just close the workbook, is also useful. However, with that routine you show us that has local variables for the app objects, what you have done is left the Excel image (EXCEL.EXE) "floating" in memory and thus not fully cleaned up. Remember that an app object runs a second image but in a way that has a life of its own. So you have to remember to do everything necessary for the maintenance of that image as though you were running it directly. I.e. shut it down ALL THE WAY.

The problem isn't that you have a remnant of what was going on in Access because when you exit that subroutine, the variables go "out of scope' and are destroyed. But Excel is ALSO still there in memory and it might have some remaining context information that Access no longer has.

 

[corinereyes]

are you sure the error isn't happening because you're not doing this before you destroy the excel instance in memory?

appExcel.quit

that might only apply though if you use the alternative:

dim xl as excel.application
set xl = new excel.application

just a thought. also, why are you opening and closing the thing 2 times in a row? that seems quite strange, and it seems like there would never be a need to do such thing.

if that doesn't work, you might want to run the "refresh microsoft query" operation from your code, IN EXCEL, using the macro recorder to see what code it shows you.[/code]

Hello!

I tried this code as you suggested but it still gives me the error.

Dim xls As Excel.Application
Dim wkb As Excel.Workbook

Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open("C:\Users\msreyes\Desktop\Finalpay.xls")

xls.Visible = True

Sheets("Tmpt").Select
ActiveWorkbook.RefreshAll

wkb.Close SaveChanges:=False

Set wkb = Nothing
xls.Quit
Set xls = Nothing

As i suspected what The_Doc_Man said. When i check the background process , excel is still running. I guess that causes the error.

Every user needs to run the button... excel needs to open , put in the criteria in excel (it fetches data from an external data source) then i save the data in access, then excel closes. I don't want the whole database to be in access because it gives a lot of issues. Any more suggestions?

Thanks for all your reply guys.

 

[corinereyes]

Very often when you see the 1004 error from an external app object it is because it is giving you a generic "I don't know what to do" error with the implication that something is internally hosed up. The last time I worked with Excel and got this error, it was because a particular object pointer had not been properly dereferenced (set to Nothing) so when I went to use it again later it was pointing to something from a prior round of instantiation. I.e. it was pointing to something inside an app object for which the app itself was now dereferenced but the internal object wasn't. Therefore they were out of sync. I can't see what else is going on here, but that is what happened with me.

Adam's suggestion to first make the app quit isn't bad. The other comment, to not close the app if you are going to use it again, just close the workbook, is also useful. However, with that routine you show us that has local variables for the app objects, what you have done is left the Excel image (EXCEL.EXE) "floating" in memory and thus not fully cleaned up. Remember that an app object runs a second image but in a way that has a life of its own. So you have to remember to do everything necessary for the maintenance of that image as though you were running it directly. I.e. shut it down ALL THE WAY.

The problem isn't that you have a remnant of what was going on in Access because when you exit that subroutine, the variables go "out of scope' and are destroyed. But Excel is ALSO still there in memory and it might have some remaining context information that Access no longer has.

i made the code as simple as possible.
"not close the app if you are going to use it again, just close the workbook, is also useful"
i might try this way.

Thanks for your reply.

 

[vba_php]

corin,

the fact that you say you're still getting the error makes no sense at all. so I'm out of advice for you. maybe richard can help. but if you're unaware of how to troubleshoot an issue like this and/or don't know what to search for, this is probably the best match to get google to do the best work for you (and it looks like the results may actually help you solve the problem):

https://www.google.com/search?q=vba+excel+object+quit+process+still+running