open excel worksheet in vb.net

[mike7510uk]

I can open the workbook but cant open the worksheet i need.
Here is the code i have so far.....

Private Sub btnstaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnstaff.Click

Dim ApExcel As Object 'To open Excel

ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.open("C:\documents and settings\mike\desktop\dss.xls")


I want to be able to define the worksheet (in this case, sheet 3)

Please help

[bgard68]

Check out post:
http://www.vbforums.com/showthread.php?t=393353

[RobDog888]

Also, my code example here - http://www.vbforums.com/showpost.php...38&postcount=3

[mike7510uk]

ok...i have tried the following but keep getting error message saying "member not found" which is found on the line with a * next to it.

Dim oExcel As Object

Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet

oExcel = CreateObject("Excel.application")
oExcel.Visible = True
oWorkBook = oExcel.Workbooks.Open("C:\documents and settings\mike\desktop\dss.xls")

* oWorkSheet = oWorkBook.Worksheets(3)*
oWorkSheet.Activate()

with regard to the (3) after worksheets, i have tried various different ways, all return the same error message.
I dont want to add any data to any cell, i just want to be able to view different sheets.

[RobDog888]

Did you add a reference to Excel?

[mike7510uk]

yes i did add the reference.

But just in case i did it wrong, please can you explain how i should have done it

thanks

[RobDog888]

Which version are you running of VB?

For 2003 its Project > Add Reference... > Select MS Excel xx.0 Object Library > Ok.

[mike7510uk]

i am running VB.net
I did do it right before, so yes i have added the refernece but am still getting the same "member not found" message

[bgard68]

Do you have 4 worksheets or 3 worksheets?

If you have 3 worksheets then try:
'Zero based!
oWorkSheet = oWorkBook.Worksheets(2)

[RobDog888]

There are 3 different year versions and multiple types of VB.NET. You can have 2002, 2003, 2005. And also, Express, Standard, Pro, Architect, etc.

[mike7510uk]

i have 6 worksheets, i can get Excel to open if i leave out the.....

'oWorkSheet = oWorkBook.Worksheets(3)
'oWorkSheet.Activate()


....code. Just cant go to the sheet i want. ( I have six sheets and six buttons, each button should open up the correct sheet)

[mike7510uk]

I am running 2003 version

[RobDog888]

See post #9.

[mike7510uk]

See post #9.

still not working...still comes up with the member not found message

[RobDog888]

Which line is the error message pointing to?

[mike7510uk]

* oWorkSheet = oWorkBook.Worksheets(3)*

thats the error line. (the (3) i have tried alternatives but still the same outcome

[RobDog888]

oWorkSheet = oWorkBook.Worksheets.Items(3)

[bgard68]

try:

oWorkSheet = oWorkBook.Worksheets("myWorkSheetName")
oWorkSheet.Activate()

[mike7510uk]

Dim oExcel As Object

Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet

oExcel = CreateObject("Excel.application")
oExcel.Visible = True
oWorkBook = oExcel.Workbooks.Open("C:\documents and settings\mike\desktop\dss.xls")



oWorkSheet = oWorkBook.Worksheets.Items(3)
oWorkSheet.Activate()

the above code is all the code i have relating to the button...

the same error message appears (even after i did the .items(3) bit)

[mike7510uk]

try:

oWorkSheet = oWorkBook.Worksheets("myWorkSheetName")
oWorkSheet.Activate()

no, still the same error message

[bgard68]

What version of Excel are you running ?

[RobDog888]

oWorkSheet = oWorkBook.Sheets.Items(2)

Forgot to make the adjustment to sheet 3. Try the Sheets collection instead.

[mike7510uk]

What version of Excel are you running ?

also 2003 for excel

[mike7510uk]

oWorkSheet = oWorkBook.Sheets.Items(2)

Forgot to make the adjustment to sheet 3. Try the Sheets collection instead.

still the same message

[RobDog888]

Not sure whats different in your workbook but this is what I usually do from my link I posted earlier.

Dim oWB As Excel.Workbook = moApp.Workbooks.Add
Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)

[mike7510uk]

Not sure whats different in your workbook but this is what I usually do from my link I posted earlier.

Dim oWB As Excel.Workbook = moApp.Workbooks.Add
Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)

where does "moApp" come from?

[PENNYSTOCK]

its been awhile, but I think you need to cast everthing.

Here is some stuff I pulled out of my code VB.net 2003 & office 2003

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Open(sFileName), Excel.Workbook)
xlSheet = CType(xlApp.ActiveSheet, Excel.Worksheet)
xlSheet = CType(xlBook.Worksheets.Item(1), Excel.Worksheet

[mike7510uk]

its been awhile, but I think you need to cast everthing.

Here is some stuff I pulled out of my code VB.net 2003 & office 2003

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Open(sFileName), Excel.Workbook)
xlSheet = CType(xlApp.ActiveSheet, Excel.Worksheet)

where does that code go?

[PENNYSTOCK]

Like this.

Dim oExcel As Object
 
Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet
 
oExcel = ctype(CreateObject("Excel.application"), Excel.Application)
oExcel.Visible = True
oWorkBook = ctype(oExcel.Workbooks.Open("C:\documents and settings\mike\desktop\dss.xls"), Excel.Workbook)
 
 
 
oWorkSheet = ctype(oWorkBook.Worksheets.Items(3),Excel.Worksheet)
oWorkSheet.Activate()

[mike7510uk]

Like this.

Dim oExcel As Object
 
Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet
 
oExcel = ctype(CreateObject("Excel.application"), Excel.Application)
oExcel.Visible = True
oWorkBook = ctype(oExcel.Workbooks.Open("C:\documents and settings\mike\desktop\dss.xls"), Excel.Workbook)
 
 
 
oWorkSheet = ctype(oWorkBook.Worksheets.Items(3),Excel.Worksheet)
oWorkSheet.Activate()

it still says "member not found" (refering to the 'oWorkSheet = ctype(oWorkBook.Worksheets.Items(3),Excel.Worksheet)' line)

[PENNYSTOCK]

One last thing to try that I can see.
Change
Dim oExcel As Object
to
Dim oExcel As Excel.Application

[mike7510uk]

still the same error message

[PENNYSTOCK]

Since you have visable set to True, do you see the excel file opening? Do you exit excel before you re-run the program?

[mike7510uk]

Since you have visable set to True, do you see the excel file opening? Do you exit excel before you re-run the program?

yes, excel opens, then the error message, when the error message is cancelled, i have to close excel. so it opens, just not on the sheet i want

[PENNYSTOCK]

repost your this function again. The whole function.

[mike7510uk]

Private Sub btnstaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnstaff.Click




Dim oExcel As Object
Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet

oExcel = CType(CreateObject("Excel.application"), Excel.Application)
oExcel.Visible = True
oWorkBook = CType(oExcel.Workbooks.Open("C:\documents and settings\mike\desktop\dss.xls"), Excel.Workbook)



oWorkSheet = CType(oWorkBook.Worksheets.Items(3), Excel.Worksheet)
oWorkSheet.Activate()



End Sub

[PENNYSTOCK]

take the "s" of item

oWorkSheet = CType(oWorkBook.Worksheets.Items(3), Excel.Worksheet)
 
should be 
 
oWorkSheet = CType(oWorkBook.Worksheets.Item(3), Excel.Worksheet)

[mike7510uk]

done that, still says the same error message.."member not found"

[RobDog888]

You never confirmed how many sheets you have in your workbook? The Items collection is zero based.

[PENNYSTOCK]

What excel do you running based on your references.

I have all three of these in my refs:

Excel 11 Ver 1.5
microsoft.office.core 11 Ver 2.3
VBIDE 5.3 ver 5.3