EXCEL Macros quit working: My Set EXCELWorkBook = Statement fails

[brianemcnamee]

Windows 7, MS Office 2007:
Recently the EXCEL Macros I've been using for years quit working. I defined the working Objects as Globals:
Global gobjEXCEL As Object
Global gobjEXCELWorkBook As Object
Global gobjEXCELWorksheet As Object

Then I use SET Statements to name the EXCEL Objects I want to use:
1) Set gobjEXCEL = CreateObject("Excel.Application")
2) Set gobjEXCELWorkBook = GetObject(pzExcelFileName)
3) Set gobjEXCELWorksheet = gobjEXCELWorkBook.Worksheets("Sheet1")

1 works, gobjEXCEL= Microsfoft Excel
2 fails, without setting ERR, subsequently 3 fails

This code has worked for a long time and only stopped working i n the last few days after a flurry of MS Updates.

Anybody know whats wrong with my Globals and/or Set Statements? I recently seemed to have a related problem ( Updates??? ) with VB6 and Access which got resolved with some Reference changes. But I haven't found a clue in the Object Browser and there aren't any Reference choices in EXCEL!

[westconn1]

1) Set gobjEXCEL = CreateObject("Excel.Application")
2) Set gobjEXCELWorkBook = GetObject(pzExcelFileName)
3) Set gobjEXCELWorksheet = gobjEXCELWorkBook.Worksheets("Sheet1")

using getobject may open the workbook in a different instance of excel to your application object (or if the workbook is already open would be sure to be in the already open application, not the one you just created), if you need to use getobject you should create the application object from the workbook object, if you actually need the application object as well as the workbook object

maybe try like

Set gobjEXCELWorkBook = GetObject(pzExcelFileName)
Set gobjEXCEL = gobjEXCELWorkBook.application
Set gobjEXCELWorksheet = gobjEXCELWorkBook.Worksheets("Sheet1")

i can not say that this is causing your problem, but at least something to be aware of
i would also look at if the path to pzexcelfilename is valid and not virtualized, as some of these can vary between updates

if the workbook will not already be open try the workbooks.open method

Set gobjEXCELWorkBook = gobjEXCEL.workbooks.open(pzExcelFileName)

i do not believe what you define your variables as would make any difference at all, the only reason to declare them as global would be to keep them valid between different procedures

[brianemcnamee]

westconn1: Thank you for the explanation/examples. Being on old mainframe system guy I just generally trusted the software to do something reasonable and it usually did. In this case I became a bit paranoid because I seem to have other examples of the same code that works. And, sometimes the code in the examples that don't work, even though they were modified a few weeks back, appeared to have significant, but critical, changes that I can't imagine doing! I'm still experimenting, but I'll try out your suggestions and let you know... BTW, I do generally access the retrieved data in various Functions and Subroutines so it needs to be Global. The application involved takes Spreadsheets I've copied from data providers and reformats them for conveniently stuffing them into an Access Db. So I have to unmerge rows, rearrange, and delete columns as well as remove various styles, background color, shapes, and et ceteras.

[GBeats]

I might be off here but i never entertained the idea of opening and getting a reference through get object etc when you can open and get a reference using excels built in methods. i do remember certain situations where i needed to use getobject if an application i had was 'Looking' for an open excel application, but actually opening a new application i would never use this method.

My point is using methods that excel provides might be a better way since they generally carry over updates and getobject which is from the good old days of vb6 might be considered stone age.

Since your using vba from within excel (it seams), use the excel methods to open files in excel.

[westconn1]

also global is a bit old school, public should be enough if placed in a standard module
also we have no idea how you are disposing of your objects when finished with them

with VB6 and Access which got resolved with some Reference changes.

the bits of code as posted requires no references