Force Excel to display toolbar

[DrSmyth]

Hi,

I'm opening an excel document from access using a form and want the external data toolbar to be displayed in Excel when it opens. This is my code:

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
Dim excApp As Excel.Application
Dim excDoc As Excel.Workbook
                    
Set excApp = CreateObject("Excel.Application")
excApp.Workbooks.Open FileName:="F:\Caseholders\Caseholder MI\Estate Static By Caseholder.xls"
excApp.Visible = True
excApp.WindowState = xlMaximized
excApp.CommandBars("External Data").Visible = True


Set excDoc = Nothing
Set excApp = Nothing




Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    End Sub

But it doesn't seem to be working (i.e. the external data toolbar is not being displayed) can anybody suggest where i'm going wrong?

p.s. operating system is windows NT and office is version 97

 

[RonRepp]

Perplexing. I tried your code and got the same result. Have you thought about creating a template with the code Application.CommandBars("External Data").Visible = True in your Workbook_Open procedure? This seems to work every time.

I hope this helps.

Ron

Ron Repp

 

[TonyJollans]

Works OK for me in 2003.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[DrSmyth]

I'll give that a go RonRepp, must admit i've not yet tried that approach

 

[combo]

Do you have "External data" toolbar available and checked when you try to customise commandbars in excel after executing this macro? If so, it is likely that this toolbar is docked and shrinked to one button + expand option. You can also try to reset it. Close this excel instance as last to save changes if you have more than one instance created.

combo

 

[DrSmyth]

Hi, Ronrepp.. Tried your suggestion and it works.. Thanks for your help and thanks to everybody else for their input