Basic VSTO questions

[riversr]

I am an absolute beginner with VSTO and Excel. I am using VS 2008 Express.
I've been able to execute a few very basic steps by creating a reference to
the Microsoft Excel 12.0 Object Library COM object. That allowed me to start
an Excel app and at least view the workbook object. My question is this. I
want to do some basic cell operations. It seems that I do not understand the
Excel object model. I assumed that if I created the Excel App, then I would
be able to access the sub-ordinate Workbooks collection and then the
Worksheets collection and eventually the cells on the worksheet, but it
hasn't worked.

Here's my code:

Excel.Application myApp = new Excel.Application();
myApp.Visible = true;
myApp.StatusBar = "Hello World";

//This line loads a workbook from an existing csv file
myApp.Workbooks.Open(@"c:\trash\xl.csv", missing, false,
missing, missing, missing, missing, missing, ",", true, missing, missing,
true, missing, missing);
//This line allows me to see the name of the workbook
string name = myApp.Workbooks[1].Name;

Then I would think that I could access the worksheet inside the workbook
with something like this, but it does not work:
string name2 = myApp.Worksbooks[1].WorkSheets[1].Name
Do I need to create a worksheet? I can see the open Excel application and
the three default worksheets are there.

Please someone, help me understand the proper way to access the worksheet
and then the cells on it. It seems there is very little information on the
web that provides good examples of this. Help!

riversr

 

[JLatham]

You are correct in your assessment of the Excel object model:
Workbooks is a collection of the open workbooks in Excel, and Worksheets is
a collection of the worksheets within a specified workbook. In Excel-ese, in
VB a cell or group of cells is a Range.
To fully address Cell on a sheet named My Test Sheet in workbook
TestBook.xls, I could use this syntax (in Excel VBA)
Dim X as Variant ' as I don't know what type will be returned
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Range("A1")
or another way to address cells is with Cell() as:
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Cell(1,1)
for .Cell first parameter is row number, second is column number.

What error are you getting with the
string name2=myApp.Workbooks[1].Worksheets[1].Name
line? You shouldn't need to create a sheet. When opening a .csv file there
should be a single sheet in the workbook, when creating a new workbook, the
number of sheets will vary depending on Excel settings. But in any case, a
workbook cannot 'exist' without at least one visible sheet, although it may
not be the first sheet in the collection.

 

[riversr]

There's really not an error, it's just that intellisense doesn't show me that
'Name' is a valid option at that point. Maybe it doesn't know the type so it
can't identify valid options to complete the command. If I just type 'Name'
there anyway, then I get a compile time error telling me that 'Name' is not
valid for the specified 'object'. Looks like I need to somehow tell C# that
this is a Worksheet object so it knows.

How do I do that elegantly?

thx

You are correct in your assessment of the Excel object model:
Workbooks is a collection of the open workbooks in Excel, and Worksheets is
a collection of the worksheets within a specified workbook. In Excel-ese, in
VB a cell or group of cells is a Range.
To fully address Cell on a sheet named My Test Sheet in workbook
TestBook.xls, I could use this syntax (in Excel VBA)
Dim X as Variant ' as I don't know what type will be returned
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Range("A1")
or another way to address cells is with Cell() as:
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Cell(1,1)
for .Cell first parameter is row number, second is column number.

What error are you getting with the
string name2=myApp.Workbooks[1].Worksheets[1].Name
line? You shouldn't need to create a sheet. When opening a .csv file there
should be a single sheet in the workbook, when creating a new workbook, the
number of sheets will vary depending on Excel settings. But in any case, a
workbook cannot 'exist' without at least one visible sheet, although it may
not be the first sheet in the collection.

I am an absolute beginner with VSTO and Excel. I am using VS 2008 Express.
I've been able to execute a few very basic steps by creating a reference to
the Microsoft Excel 12.0 Object Library COM object. That allowed me to start
an Excel app and at least view the workbook object. My question is this. I
want to do some basic cell operations. It seems that I do not understand the
Excel object model. I assumed that if I created the Excel App, then I would
be able to access the sub-ordinate Workbooks collection and then the
Worksheets collection and eventually the cells on the worksheet, but it
hasn't worked.

Here's my code:

Excel.Application myApp = new Excel.Application();
myApp.Visible = true;
myApp.StatusBar = "Hello World";

//This line loads a workbook from an existing csv file
myApp.Workbooks.Open(@"c:\trash\xl.csv", missing, false,
missing, missing, missing, missing, missing, ",", true, missing, missing,
true, missing, missing);
//This line allows me to see the name of the workbook
string name = myApp.Workbooks[1].Name;

Then I would think that I could access the worksheet inside the workbook
with something like this, but it does not work:
string name2 = myApp.Worksbooks[1].WorkSheets[1].Name
Do I need to create a worksheet? I can see the open Excel application and
the three default worksheets are there.

Please someone, help me understand the proper way to access the worksheet
and then the cells on it. It seems there is very little information on the
web that provides good examples of this. Help!

riversr

 

[riversr]

I figured it out. I added this line so that I know that the object is a
worksheet:

Excel.Worksheet worksheet = myApp.Workbooks[1].Worksheets[1] as
Excel.Worksheet;

Now this works:

string name = worksheet.Name;


Thanks for the help. I'm sure I'll have more questions.

thanks again...

There's really not an error, it's just that intellisense doesn't show me that
'Name' is a valid option at that point. Maybe it doesn't know the type so it
can't identify valid options to complete the command. If I just type 'Name'
there anyway, then I get a compile time error telling me that 'Name' is not
valid for the specified 'object'. Looks like I need to somehow tell C# that
this is a Worksheet object so it knows.

How do I do that elegantly?

thx

You are correct in your assessment of the Excel object model:
Workbooks is a collection of the open workbooks in Excel, and Worksheets is
a collection of the worksheets within a specified workbook. In Excel-ese, in
VB a cell or group of cells is a Range.
To fully address Cell on a sheet named My Test Sheet in workbook
TestBook.xls, I could use this syntax (in Excel VBA)
Dim X as Variant ' as I don't know what type will be returned
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Range("A1")
or another way to address cells is with Cell() as:
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Cell(1,1)
for .Cell first parameter is row number, second is column number.

What error are you getting with the
string name2=myApp.Workbooks[1].Worksheets[1].Name
line? You shouldn't need to create a sheet. When opening a .csv file there
should be a single sheet in the workbook, when creating a new workbook, the
number of sheets will vary depending on Excel settings. But in any case, a
workbook cannot 'exist' without at least one visible sheet, although it may
not be the first sheet in the collection.

I am an absolute beginner with VSTO and Excel. I am using VS 2008 Express.
I've been able to execute a few very basic steps by creating a reference to
the Microsoft Excel 12.0 Object Library COM object. That allowed me to start
an Excel app and at least view the workbook object. My question is this. I
want to do some basic cell operations. It seems that I do not understand the
Excel object model. I assumed that if I created the Excel App, then I would
be able to access the sub-ordinate Workbooks collection and then the
Worksheets collection and eventually the cells on the worksheet, but it
hasn't worked.

Here's my code:

Excel.Application myApp = new Excel.Application();
myApp.Visible = true;
myApp.StatusBar = "Hello World";

//This line loads a workbook from an existing csv file
myApp.Workbooks.Open(@"c:\trash\xl.csv", missing, false,
missing, missing, missing, missing, missing, ",", true, missing, missing,
true, missing, missing);
//This line allows me to see the name of the workbook
string name = myApp.Workbooks[1].Name;

Then I would think that I could access the worksheet inside the workbook
with something like this, but it does not work:
string name2 = myApp.Worksbooks[1].WorkSheets[1].Name
Do I need to create a worksheet? I can see the open Excel application and
the three default worksheets are there.

Please someone, help me understand the proper way to access the worksheet
and then the cells on it. It seems there is very little information on the
web that provides good examples of this. Help!

riversr

 

[JLatham]

Well, glad you resolved the issue. My problem in providing help in this case
is that my knowledge of the current version of VB as in Visual Studio 2008 or
2010, is to rusty and out of date so as to be pretty useless - it got that
way when VB started turning into more of a front end for databases than the
general purpose tool it originally was. And then there seemed to be more
call for VBA among my contacts than for pure VB, and so here I am.
And I've never been able to find the time to learn any version of C at all,
which in itself is a personal disappointment. And yes, I realize you're
doing your work in VC.

I figured it out. I added this line so that I know that the object is a
worksheet:

Excel.Worksheet worksheet = myApp.Workbooks[1].Worksheets[1] as
Excel.Worksheet;

Now this works:

string name = worksheet.Name;


Thanks for the help. I'm sure I'll have more questions.

thanks again...

There's really not an error, it's just that intellisense doesn't show me that
'Name' is a valid option at that point. Maybe it doesn't know the type so it
can't identify valid options to complete the command. If I just type 'Name'
there anyway, then I get a compile time error telling me that 'Name' is not
valid for the specified 'object'. Looks like I need to somehow tell C# that
this is a Worksheet object so it knows.

How do I do that elegantly?

thx

You are correct in your assessment of the Excel object model:
Workbooks is a collection of the open workbooks in Excel, and Worksheets is
a collection of the worksheets within a specified workbook. In Excel-ese, in
VB a cell or group of cells is a Range.
To fully address Cell on a sheet named My Test Sheet in workbook
TestBook.xls, I could use this syntax (in Excel VBA)
Dim X as Variant ' as I don't know what type will be returned
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Range("A1")
or another way to address cells is with Cell() as:
X = Workbooks("TestBook.xls").Worksheets("My Test Sheet").Cell(1,1)
for .Cell first parameter is row number, second is column number.

What error are you getting with the
string name2=myApp.Workbooks[1].Worksheets[1].Name
line? You shouldn't need to create a sheet. When opening a .csv file there
should be a single sheet in the workbook, when creating a new workbook, the
number of sheets will vary depending on Excel settings. But in any case, a
workbook cannot 'exist' without at least one visible sheet, although it may
not be the first sheet in the collection.


:

I am an absolute beginner with VSTO and Excel. I am using VS 2008 Express.
I've been able to execute a few very basic steps by creating a reference to
the Microsoft Excel 12.0 Object Library COM object. That allowed me to start
an Excel app and at least view the workbook object. My question is this. I
want to do some basic cell operations. It seems that I do not understand the
Excel object model. I assumed that if I created the Excel App, then I would
be able to access the sub-ordinate Workbooks collection and then the
Worksheets collection and eventually the cells on the worksheet, but it
hasn't worked.

Here's my code:

Excel.Application myApp = new Excel.Application();
myApp.Visible = true;
myApp.StatusBar = "Hello World";

//This line loads a workbook from an existing csv file
myApp.Workbooks.Open(@"c:\trash\xl.csv", missing, false,
missing, missing, missing, missing, missing, ",", true, missing, missing,
true, missing, missing);
//This line allows me to see the name of the workbook
string name = myApp.Workbooks[1].Name;

Then I would think that I could access the worksheet inside the workbook
with something like this, but it does not work:
string name2 = myApp.Worksbooks[1].WorkSheets[1].Name
Do I need to create a worksheet? I can see the open Excel application and
the three default worksheets are there.

Please someone, help me understand the proper way to access the worksheet
and then the cells on it. It seems there is very little information on the
web that provides good examples of this. Help!

riversr