Question Opening an existing Excell File

[Toolbit1]

I am trying to get an exsisting file to open from my shared drive over a network using Excell and I am not having any luck. Can anyone please asssist me with this.

My code is below.

Private Sub cmbControl_Chart_Exl_Click()
On Error GoTo Err_cmbControl_Chart_Exl_Click
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True
Exit_cmbControl_Chart_Exl_Click:
    Exit Sub
Err_cmbControl_Chart_Exl_Click:
    MsgBox Err.Description
    Resume Exit_cmbControl_Chart_Exl_Click
 
End Sub

 

[boblarson]

Private Sub cmbControl_Chart_Exl_Click()
On Error GoTo Err_cmbControl_Chart_Exl_Click
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    [COLOR="Red"][B]oApp.Workbooks.Open("YourNameAndPathToTheFile")[/B][/COLOR]
    oApp.UserControl = True
[B][COLOR="Red"]    Set oapp = Nothing[/COLOR][/B]
Exit_cmbControl_Chart_Exl_Click:
    Exit Sub
Err_cmbControl_Chart_Exl_Click:
    MsgBox Err.Description
    Resume Exit_cmbControl_Chart_Exl_Click
 
End Sub

And UserControl is NOT just for 97 but works in all versions. Don't know where you got that idea, but it does when doing it via automation like this.

But you should destroy your variable.

 

[Toolbit1]

Thanks. That got it.

 

[Toolbit1]

If I want the program to open a certain worksheet in this excel workbook that was opened, how do I do that?

 

[boblarson]

Then you can modify the code as follows:

Private Sub cmbControl_Chart_Exl_Click()
On Error GoTo Err_cmbControl_Chart_Exl_Click
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    oApp.Workbooks.Open("YourNameAndPathToTheFile")
[COLOR="Red"]    oApp.Worksheets("YourWorksheetNameHere").Activate[/COLOR]
    oApp.UserControl = True
    Set oapp = Nothing
Exit_cmbControl_Chart_Exl_Click:
    Exit Sub
Err_cmbControl_Chart_Exl_Click:
    MsgBox Err.Description
    Resume Exit_cmbControl_Chart_Exl_Click
 
End Sub

 

[boblarson]

That was untested, and so is this. You might have to use this code instead, now that I think about it:

Private Sub cmbControl_Chart_Exl_Click()
On Error GoTo Err_cmbControl_Chart_Exl_Click
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    oApp.Workbooks.Open("YourNameAndPathToTheFile")
    [COLOR="red"]oApp.[/COLOR][COLOR="Blue"]ActiveWorkbook.[/COLOR][COLOR="Red"]Worksheets("YourWorksheetNameHere").Activate[/COLOR]
    oApp.UserControl = True
    Set oapp = Nothing
Exit_cmbControl_Chart_Exl_Click:
    Exit Sub
Err_cmbControl_Chart_Exl_Click:
    MsgBox Err.Description
    Resume Exit_cmbControl_Chart_Exl_Click
 
End Sub

 

[Toolbit1]

The first one worked great. Thanks again.

 

[boblarson]

No problem, glad to be able to help.