Workbook not always visible when opened in VBA 1

[VicM]

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:

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

 

[SkipVought]

Hi,


Try this...

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

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

Vic

 

[VicM]

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

 

[SkipVought]

Check out faq707-4594.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

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

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.

 

[VicM]

Andy,

That makes sense to me.

So you suggest I do the following?

Dim xl as Excel
Dim fso as FileSystem

Are those the correct syntax?

Vic

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

(Classic Consultant's response ) - 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.

 

[VicM]

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

 

[combo]

Having proper references for early binding the declarations are:
[tt]Dim xl as Excel.Application
Dim fso as Scripting.FileSystemObject[/tt]
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

 

[Andrzejek]

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

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.

 

[VicM]

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

 

[Andrzejek]

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.

 

[DjangMan]

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):

'*** 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

 

[VicM]

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