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
225 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 45

Expert Comment

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

Author Comment

by:pabrann
Comment Utility
They are in the declaration section..of a standard module
0
 

Author Comment

by:pabrann
Comment Utility
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
 
LVL 33

Expert Comment

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

Open in new window

0
 

Author Comment

by:pabrann
Comment Utility
do this in the form module subroutine? I've never tried it.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 250 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 33

Assisted Solution

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

Author Closing Comment

by:pabrann
Comment Utility
Excellent, it works now. thanks so much
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 33

Expert Comment

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

Author Comment

by:pabrann
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

12 Experts available now in Live!

Get 1:1 Help Now