Solved

Strange Behavior in Public Excel Object

Posted on 2013-11-11
7
212 Views
Last Modified: 2014-02-14
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.
0
Comment
Question by:Buck_Beasom
  • 3
  • 2
  • 2
7 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 39638997
<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
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
ID: 39639072
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
 

Author Comment

by:Buck_Beasom
ID: 39639236
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39639246
@Buck_Beasom,

better post all the codes you are using regarding this process so we can  pinpoint exactly why your codes is failing.
0
 
LVL 33

Expert Comment

by:Norie
ID: 39639302
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
 

Author Comment

by:Buck_Beasom
ID: 39642120
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
 
LVL 33

Expert Comment

by:Norie
ID: 39859924
Capricorn?

An invisible contributor to this question?:)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now