Solved

Strange Behavior in Public Excel Object

Posted on 2013-11-11
7
219 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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