• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

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
pabrann
Asked:
pabrann
  • 5
  • 4
  • 3
2 Solutions
 
Martin LissOlder than dirtCommented:
Define the two Excel objects in the Declarations section of a module.
0
 
pabrannPresidentAuthor Commented:
They are in the declaration section..of a standard module
0
 
pabrannPresidentAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
NorieVBA ExpertCommented:
If you add this does it help?
objExcelWb.UserControl = True

Open in new window

0
 
pabrannPresidentAuthor Commented:
do this in the form module subroutine? I've never tried it.
0
 
Martin LissOlder than dirtCommented:
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
 
NorieVBA ExpertCommented:
The code I suggested would go just before you call Read_Excel.
0
 
pabrannPresidentAuthor Commented:
Excellent, it works now. thanks so much
0
 
Martin LissOlder than dirtCommented:
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
 
NorieVBA ExpertCommented:
Just curious, what was it that you changed that made things work?
0
 
pabrannPresidentAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now