asked on

OPen a Specific Excel sheet from a VB Button.

OK, I can open an Excel Spreadsheet from a button, but how do I open a specific sheet inside the Excel Workbook.

Here is the code to open an excel Workbook:

Set ExcelWBk = Excel.Workbooks.Open("H:\Comets Portland\Comets\Excel\Queue Age\que aging" )

How would I make it open a worksheet name "HB PRIME"?

David Lee

This works for me.

ExcelWbk.Sheets("SheetName").Activate

spzbiegien

ASKER

It doesn't work for me! Where would you add the code to make it work. See Code Below!

Private Sub CmdQuarter3_Click()
Dim Excel As Excel.Application
Dim ExcelWBk As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Form1.Animation1.Open "H:\Comets Portland\Comets\comtest2.avi"
Form1.Animation1.Visible = True
Set Excel = CreateObject("Excel.Application")
With Excel
.Visible = True
End With

Set ExcelWBk = Excel.Workbooks.Open("H:\Comets Portland\COMETS\Excel\CometsForecasts\Q3 2004")
Set ExcelWS = Excel.Sheets("CIB Forcast").Activate
Form1.Animation1.Visible = False
Exit Sub

End Sub

David Lee

Two problems. First, use the workbook object, not the Excel object. Second, don't use Set on the command to activate a sheet. In the code above replace

Set ExcelWS = Excel.Sheets("CIB Forcast").Activate

with

ExcelWBk.Sheets("CIB Forcast").Activate

spzbiegien

ASKER

i GET RUN TIME ERROR "9" SUBSCRIPT OUT OF RANGE

David Lee

That sounds to me as if the sheet name isn't correct. I created a workbook here and added a sheet with the name "CIB Forcast" and tested the code. It worked fine.

spzbiegien

ASKER

Thank you, I Spelled Forecast wrong!

David Lee

No problem, I do things like that all the time.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial