Solved

Microsoft Access VBA declared public Excel objects in standard module, are erroneously closing after running subroutine in form module

Posted on 2015-01-11
12
236 Views
Last Modified: 2015-01-12
Variables declared in standard module named basExcel:

Public objExcelAp As Excel.Application
Public objExcelWb As Excel.Workbook


Form module subroutine to set objects and reads excel spreadsheet:

Sub Open_Excel()
      Set objExcelAp = New Excel.Application
      Set objExcelWb = objExcelAp.Workbooks.Open(sFileName)
      Read_Excel
End Sub   (objects close here after the end sub executes)

After this routine ends, the excel objects are closing without my code making that happen. I need them to stay open and be closed later with my vba code after other unrelated procedures are executed in another form module.
There is more detail in these procedures but I trimmed for visibility.
0
Comment
Question by:pabrann
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40543535
Define the two Excel objects in the Declarations section of a module.
0
 

Author Comment

by:pabrann
ID: 40543536
They are in the declaration section..of a standard module
0
 

Author Comment

by:pabrann
ID: 40543539
I initially had them declared in the declaration section using the Dim statement which should have worked. I then changed them to Public. Still the same problem
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 34

Expert Comment

by:Norie
ID: 40543551
If you add this does it help?
objExcelWb.UserControl = True

Open in new window

0
 

Author Comment

by:pabrann
ID: 40543556
do this in the form module subroutine? I've never tried it.
0
 
LVL 48

Accepted Solution

by:
Martin Liss earned 250 total points
ID: 40543582
I initially had them declared in the declaration section using the Dim statement which should have worked. I then changed them to Public. Still the same problem
Changing from Dim (= Private) to public only changes the scope of the objects. When Dim or Private they can only be used in that module, but when Public they can be used anywhere.
0
 
LVL 34

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 40543585
The code I suggested would go just before you call Read_Excel.
0
 

Author Closing Comment

by:pabrann
ID: 40544265
Excellent, it works now. thanks so much
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40544289
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
LVL 34

Expert Comment

by:Norie
ID: 40544723
Just curious, what was it that you changed that made things work?
0
 

Author Comment

by:pabrann
ID: 40544864
It's embarrassing to say but I had originally declared the variables at the form level. When that didn't work, I declared them in a standard module. But, I failed to rem or delete the declarations in the form.. It was my error. But reading the comments from above lead to me finding my mistake.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40544877
Don't be embarrassed. It's a common enough thing to have happen that there's a name for it - "name shadowing". As you found out, when there are more than one variable with the same name, the one with the smallest scope "shadows" the others and makes them unavailable.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

627 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