open excel worksheet in vb.net

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

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

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

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.

Did you add a reference to Excel?

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

Which version are you running of VB?

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

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

Do you have 4 worksheets or 3 worksheets?

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

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.

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)

I am running 2003 version

See post #9.

Quote:

---

Originally Posted by RobDog888

See post #9.

---

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

Which line is the error message pointing to?

* oWorkSheet = oWorkBook.Worksheets(3)*

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

VB Code:

oWorkSheet = oWorkBook.Worksheets.Items(3)

try:

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

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)

Quote:

---

Originally Posted by bgard68

try:

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

---

no, still the same error message

What version of Excel are you running ?

VB Code:

oWorkSheet = oWorkBook.Sheets.Items(2)

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

Quote:

---

Originally Posted by bgard68

What version of Excel are you running ?

---

also 2003 for excel

Quote:

---

Originally Posted by RobDog888

VB Code:

oWorkSheet = oWorkBook.Sheets.Items(2)

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

---

still the same message

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

VB Code:

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

Quote:

---

Originally Posted by RobDog888

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

VB Code:

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

---

where does "moApp" come from?

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

VB Code:

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

Quote:

---

Originally Posted by 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

VB Code:

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?

Like this.

VB Code:

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

Quote:

---

Originally Posted by PENNYSTOCK

Like this.

VB Code:

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)

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

still the same error message

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

Quote:

---

Originally Posted by 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?

---

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

repost your this function again. The whole function.

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

take the "s" of item

VB Code:

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

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

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

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

Quote:

---

Originally Posted by RobDog888

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

---

It is 1 based, not zero based.

Posted you code in my program and it worked perfectly.

Maybe its the file you are trying to open? Try another.

VB Code:

Private Sub bOpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bOpenFile.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("J:\Documents and Settings\DavidH\My Documents\Blank.xls"), Excel.Workbook)
 
 
            oWorkSheet = CType(oWorkBook.Worksheets.Item(2), Excel.Worksheet)
            oWorkSheet.Activate()
End

There has to be something else going on here...
Not sure what, so Ill have to do some more digging...

I got this code to work when i created a new project. It still doesnt work on my original one, any ideas why?

Private Sub bopen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bopen.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.Item(4), Excel.Worksheet)
oWorkSheet.Activate()
End Sub

Quote:

---

Originally Posted by PENNYSTOCK

It is 1 based, not zero based.

---

My mistake. Confused Items with Sheets. :blush:

ok, give up now. Going to use excel and vba instead (which is bound to cause me more problems!!!)

Cheers for your help guys

Can you compare your projects side by side for differences in references, code lines, etc.?

I think the problem was that the original excel book i was opening was pre used and contained un-deleted macros. Whereas the new workbook i tried didnt and worked fine