?
Solved

Strange Behavior in Public Excel Object

Posted on 2013-11-11
7
Medium Priority
?
224 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 1000 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 34

Accepted Solution

by:
Norie earned 1000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 34

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 34

Expert Comment

by:Norie
ID: 39859924
Capricorn?

An invisible contributor to this question?:)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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