Solved

Strange Behavior in Public Excel Object

Posted on 2013-11-11
7
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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