Strange Behavior in Public Excel Object

I am using an Excel object to open and populate various Excel template files with validation data. Because the validations are sometimes run together in batches and other times individually, the validation routines are in their own functions that can be called as needed. I use this code to setup the Excel:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open modGlobal.g_strCode_Validations_Template
objExcel.ActiveWorkbook.SaveAs modGlobal.g_strCode_Validations_TemplateFile

Here's what's wierd. Behind one button, this all works fine. All the functions run and are completed as they should be. But behind another one, the code kicks me out of the function with the first reference to the Excel object. Like this:

objExcel.Worksheets("Master Validation One").Activate

What's even wierder is that when I call the functions individually, and run the 5 lines of code above to set up the Excel object and open the file, the functions run. It is only when the function is called from the batching routine that it kicks me out.

Any suggestions would be appreciated.

Thanks.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Does this help, or give any insight on the problem?
Dim objWB As Object ' Excel.Workbook

    Set objExcel = CreateObject("Excel.Application") 
    objExcel.Visible = True
    'objExcel.DisplayAlerts = False

    Set objWB = objExcel.Workbooks.Open modGlobal.g_strCode_Validations_Template
    objWB.SaveAs modGlobal.g_strCode_Validations_TemplateFile

Open in new window

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<It is only when the function is called from the batching routine that it kicks me out.>

i believe you are getting an error... (pertaining to method or object)

you need to run the 5 lines of codes to set up the Excel object and open the file,


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open modGlobal.g_strCode_Validations_Template
objExcel.ActiveWorkbook.SaveAs modGlobal.g_strCode_Validations_TemplateFile

objExcel.Worksheets("Master Validation One").Activate

to have a reference to the object  objExcel
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
OK. There are two separate suggestions here.

Capricorn, are you suggesting that the "objExcel.Worksheets("Master Validation One") code must be executed BEFORE calling the function? Not a show-stopper if necessary, but it wasn't in the other instance that worked.

imnorie: Only difference between your code and mine is the "Set" before I open the workbook. I've never used that before and the "Open" has always worked. Is there something I am missing?

Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
@Buck_Beasom,

better post all the codes you are using regarding this process so we can  pinpoint exactly why your codes is failing.
0
 
NorieVBA ExpertCommented:
Buck

That's not the only difference.

The code I posted doesn't use ActiveWorkbook and the line disabling screen alerts is commented out - having alerts disabled might be hiding something.
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
Capricorn:

You have saved my bacon so many times in the past I feel like I owe you a bottle of Laphroaig, but here goes:

The program runs a bunch of imports and validations. What I want to do is have a "master button" that does the import and then runs a series of validations - opening an Excel template with a tab for each validation and printing errors on that tab. Then, for each validation, there is a second function that lets the user manually pass the validation step if they want. Results get written into a table that is examined by the form. So, for example, the "Code Validations" routine for the example attached, runs 4 validations initially and writes the results. Beneath the master Code Validation button are 4 "subordinate" buttons, one for each test. They have black backgrounds. Untested ones have white letters, fails have red, "user forced" passes are yellow and "clean passes" are green.

I originally had the master button opening the Excel - which is declared as a public object - and then calling the function that does the validation. This worked for the first set of validations, but for all subsequent ones I was getting blown out of the function on the first reference to the Excel object. Since they are functions, it doesn't stop you, it just kicks you out. Normally, to debug this, I pull the code out of the function and back up behind the launch button and replace the call, but it WORKS up there.

The intDettoImpValMode variable tells the function whether the Excel is already open (for the "all in one" functions) or needs to be opened (for the individual calls.) The functions work for the individual calls, when the Excel Object variable is being set up within the function.

You'll see a lot of stuff commented out - which was necessary when I pulled the function code back up behind the master button. The code runs this way, but it is really clunky as I have the same code that is used in the function repeated behind the master button  because the function blows it up - but only sometimes. I just couldn't wait any longer to proceed because I need the app testable by the December for year end. Obviously, what I really want is to be able to call the functions from EITHER the master button or the individual buttons (for retest.) I was real proud of the elegant "function approach" except I couldn't figure out why the same code works behind one button/function combination and blows up under another.

This is not the first time that I have had problems with a public Excel object not behaving in functions. I declare it public way up at the top (under the Option Compare Database line). The only thing I can think of is making the button sub public rather than private, but it works as a private in another situation with the exact same code.

Any insights will be appreciated.
0
 
NorieVBA ExpertCommented:
Capricorn?

An invisible contributor to this question?:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.