×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Workbook not always visible when opened in VBA

Workbook not always visible when opened in VBA

Workbook not always visible when opened in VBA

(OP)
Hey folks,

Another head scratcher here.

Have a button on a form which is supposed to open an Excel (2010, xlsm) spreadsheet. It did open once or twice where it was visible to the user. But now, even tho it does launch Excel, as seen in the task manager, it doesn't show the spreadsheet on screen. So if I try again, I get another instance of Excel showing in the task manager

When it did open visibly, closing the spreadsheet then closed out the Excel application and was no longer a process in task manager. But now, I have to manually end the process or they just keep piling up.

Here is the button code:

CODE

Private Sub btnTalHdrs_Click()
Dim xl As Object
Dim wb As Workbook
Dim fso As Object
Dim fn As String

Set fso = CreateObject("Scripting.FileSystemObject")

fn = "F:\DBMS\Membership\Membership\TalReqColHdrs.xlsm"

If fso.FileExists(fn) = True Then
    
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Open(fn)

Else
    Beep
    MsgBox "Talen header template file does not exist.  Please notify programmer.", vbOKOnly, "Can't Find File"
    DoCmd.Close acForm, Me.Name, acSaveNo
    
End If

Set fso = Nothing

End Sub 

Any suggestion?

Thanks,
Vic

RE: Workbook not always visible when opened in VBA

Hi,


Try this...

CODE

If fso.FileExists(fn) = True Then
    
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Open(fn)
    xl.Visible = TRUE

Else
    Beep
    MsgBox "Talen header template file does not exist.  Please notify programmer.", vbOKOnly, "Can't Find File"
    DoCmd.Close acForm, Me.Name, acSaveNo
    
End If 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Workbook not always visible when opened in VBA

(OP)
Dang it Skip! Wish everything was that simple.
A well deserved star.

Vic

RE: Workbook not always visible when opened in VBA

(OP)
Also just wanted to mention that I don't get the intellisense drop downs on a lot of the objects I use. I suppose that's why some of the answers here make perfect sense if I was able to see the selections available to me.

Thanks,
Vic

RE: Workbook not always visible when opened in VBA

Check out FAQ707-4594: How to use the Watch Window as a Power Programming Tool.

Although not a replacement for intellisense drop downs, which I, too, rely on and sometimes don’t see either, the Watch Window and Object Browser does give you a “list” of properties for objects, although certainly not as handy as intellisense drop downs for coding.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Workbook not always visible when opened in VBA

"I don't get the intellisense" with Excel object, I assume:

CODE

Dim xl As Object
...
Set xl = CreateObject("Excel.Application")
... 

Because you are using late binding, and not early binding.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Workbook not always visible when opened in VBA

(OP)
Andy,

That makes sense to me.

So you suggest I do the following?

CODE

Dim xl as Excel
Dim fso as FileSystem 

Are those the correct syntax?

Vic

RE: Workbook not always visible when opened in VBA

Only if you have a library reference checked in Tools > References for Excel. Don’t know about FSO.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Workbook not always visible when opened in VBA

(Classic Consultant's response smile ) - well, that all depends...
Early vs. late biding depends on what you want, and what you have available (on client machine). If you know for sure the client has Excel 2016 installed and they don't want to upgrade, and you want to have intellisense while coding - use early binding.

If not, use (early binding while coding to have intellisense available to you, and then change it to - with some possible modifications, usually not much ot of) late binding.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Workbook not always visible when opened in VBA

(OP)
FSO requires Microsoft Scripting Runtime reference.
I made those changes and the subroutine still works as planned. Yipee! LOL

RE: Workbook not always visible when opened in VBA

Having proper references for early binding the declarations are:
Dim xl as Excel.Application
Dim fso as Scripting.FileSystemObject

The first part in variable type is library name, can be ommited if there are no types with the same name in higher priority references (higher in the checked references list. However, it is a good practice to include library name if it is not the compulsory reference.

combo

RE: Workbook not always visible when opened in VBA

"FSO requires Microsoft Scripting Runtime reference." - no, it does not (with the code you provided originally):

CODE

Dim fso As Object
...
Set fso = CreateObject("Scripting.FileSystemObject")
... 

Again, early vs. late binding (you had/have in code late binding)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Workbook not always visible when opened in VBA

(OP)
Andy it does when using early binding which I switched to from late binding. Sorry I wasn't clearer when I made the above statement.

Combo, thanks for the clarification.

Thanks to you both.

Vic

RE: Workbook not always visible when opened in VBA

Yes, you have to have reference set if you use early binding.
For late binding reference is superfluous

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Workbook not always visible when opened in VBA

Quote (Andrzejek)

For late binding reference is superfluous

If the code is contained within an Office document then, I agree, it is superfluous.

In my line of work VBA is hosted by another program, not Office. When I automate Excel I can't guarantee that my installed version of Excel is the same as the target computer. In my code module where I have my Excel related functions I have it set up like this (complete with comments to myself):

CODE --> VBA

'*** Note to developer.

'    When programming uncomment these three lines and
'    comment the three after that.  That allows the use of Intellisense to
'    assist code creation.
'    You'll also have to add in a reference to your Excel Object Library

'    Before delivering remove the reference to the Excel Object Library and
'    comment out the lines with "Excel." because the macro won't know
'    what "Excel.Application" is.  This allows the macro to work on all versions of Excel.

'** Development start **
'Public xlApp As Excel.Application
'Public theBook As Excel.Workbook
'Public theSheet As Excel.Worksheet
'** Development end **

'** End-user deployment start **
Public xlApp As Object
Public theBook As Object
Public theSheet As Object 

RE: Workbook not always visible when opened in VBA

(OP)
DjangMan,

Thanks for your insights.

Because I maintain just this one Access DB that requires interactivity with Excel, all of my coding is contained under the umbrella of Microsoft applications.

But your suggestions are more ammo for me to work with should that change.

Thanks again,
Vic

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close