G
Guest
I need to do some research on how to use excel automation from c#. Does
anyone know of any good books related to this subject?
Thanks.
Mansi
anyone know of any good books related to this subject?
Thanks.
Mansi
J
Joe Mayo
Hi Mansi,
Here's a collection of relevant information on MSDN:
http://msdn.microsoft.com/office/understanding/excel/default.aspx
Joe
Here's a collection of relevant information on MSDN:
http://msdn.microsoft.com/office/understanding/excel/default.aspx
Joe
G
Guest
Thanks. This link contains lots of helpful info.
Which discussion group is the best place to post technical code related
questions related to c# and excel automation?
Mansi
Which discussion group is the best place to post technical code related
questions related to c# and excel automation?
Mansi
J
Joe Mayo
I think you should get good responses in the
microsoft.public.vsnet.vstools.office group. That one is for Visual Studio
Tools for Office, but I think whether you use VSTO or PIA's you'll see many
of the same issues.
Another good newsgroup is microsoft.public.framework.interop, especially if
you are using PIA's because they will discuss general COM Interop issues
that may or may not have anything to do with Excel.
Joe
microsoft.public.vsnet.vstools.office group. That one is for Visual Studio
Tools for Office, but I think whether you use VSTO or PIA's you'll see many
of the same issues.
Another good newsgroup is microsoft.public.framework.interop, especially if
you are using PIA's because they will discuss general COM Interop issues
that may or may not have anything to do with Excel.
Joe
G
Guest
I've done some reading on PIA's and know that they are available for download
for office XP, but what is VSTO?
Can excel automation be used so that I can export data from Visual c# .NET
to an excel worksheet? What I'm looking to do is export data from a grid in
C# to excel such that the data is nicely formated and easy to read. (I'm
assuming that the "Record Macro" function in Excel will help simplify the
code for me). Is this something that's very complicated to do once I have
installed the Office XP PIAs?
Thanks.
Venu
for office XP, but what is VSTO?
Can excel automation be used so that I can export data from Visual c# .NET
to an excel worksheet? What I'm looking to do is export data from a grid in
C# to excel such that the data is nicely formated and easy to read. (I'm
assuming that the "Record Macro" function in Excel will help simplify the
code for me). Is this something that's very complicated to do once I have
installed the Office XP PIAs?
Thanks.
Venu
J
Joe Mayo
VSTO == Visual Studio Tools for Office. It ships with VS.NET Architect or
you can buy it as a separate package. It allows you to automate Excel and
Word applications with managed code. The programming model reminds me of
ASP.NET with code-behind, where you can use Excel or Word as your UI. The
only problem I had with it was that it supported only a single document and
in the project I was working on, the customer needed to open multiple
instances at the same time. However, if your requirements allow you to work
with a single document, this is an excellent way to develop Office apps.
If you are not using VSTO, Primary Interop Assemblies (PIAs) are the way to
go. They are released by Microsoft and strong named and will save you many
headaches. If someone should choose to generate their own interop
assemblies, a whole new world of problems opens up, so I don't recommend it.
For example, in self-generated interop assemblies, the SinkHelper methods
are generated with private accessibility. You need these to be public in
order to receive callbacks for Office document events. This requires you to
disassemble the assembly, make the SinkHelper's public, and reassemble the
assembly. PIAs save you all this hassle and more.
You can do nearly everything you want with Excel, including putting data in
the spread-sheet and formatting it as you like, pulling information out,
capturing spreadsheet events, and more. The recorder macro is your friend
because you can do what you want, generate the code, and examine the code to
see what you need to do in C#.
Joe
you can buy it as a separate package. It allows you to automate Excel and
Word applications with managed code. The programming model reminds me of
ASP.NET with code-behind, where you can use Excel or Word as your UI. The
only problem I had with it was that it supported only a single document and
in the project I was working on, the customer needed to open multiple
instances at the same time. However, if your requirements allow you to work
with a single document, this is an excellent way to develop Office apps.
If you are not using VSTO, Primary Interop Assemblies (PIAs) are the way to
go. They are released by Microsoft and strong named and will save you many
headaches. If someone should choose to generate their own interop
assemblies, a whole new world of problems opens up, so I don't recommend it.
For example, in self-generated interop assemblies, the SinkHelper methods
are generated with private accessibility. You need these to be public in
order to receive callbacks for Office document events. This requires you to
disassemble the assembly, make the SinkHelper's public, and reassemble the
assembly. PIAs save you all this hassle and more.
You can do nearly everything you want with Excel, including putting data in
the spread-sheet and formatting it as you like, pulling information out,
capturing spreadsheet events, and more. The recorder macro is your friend
because you can do what you want, generate the code, and examine the code to
see what you need to do in C#.
Joe
G
Guest
I'm now able to successfully open up and write to an excel workbook.
Thanks for all the help
Mansi
Thanks for all the help
Mansi
G
Guest
I used the Record Macro function to record inputing names in 2 different
cells. The macro returned the following:
***********************************
Range("A1").Select
ActiveCell.FormulaR1C1 = "John"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Smith"
***********************************
How should I go about trying to figure out how to convert this to C# code?
For ex, I know how to open up an existing worksheet c#, but what next?
Assuming m_objSheet is of type Excel._Worksheet,
m_objSheet.Range("A1").Select is not valid.
Is there any reference material available for this topic?
Thanks
Mansi
cells. The macro returned the following:
***********************************
Range("A1").Select
ActiveCell.FormulaR1C1 = "John"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Smith"
***********************************
How should I go about trying to figure out how to convert this to C# code?
For ex, I know how to open up an existing worksheet c#, but what next?
Assuming m_objSheet is of type Excel._Worksheet,
m_objSheet.Range("A1").Select is not valid.
Is there any reference material available for this topic?
Thanks
Mansi
J
Joe Mayo
Hi Mansi,
It would probably be worth your effort to check out the Office Developer
Center for Excel, which has many resources to help out.
http://msdn.microsoft.com/office/understanding/excel/default.aspx
In particular, look at the Excel articles Ken Getz wrote arount the time
VSTO was being released. Many of the coding techniques are the same whether
you are using VSTO or PIAs. Here's a real good one to read:
http://msdn.microsoft.com/office/un...brary/en-us/odc_vsto2003_ta/html/excelobj.asp
Go straight to the section on "The Range Object" and it will give you some
examples that pertain to this particular question.
Joe
It would probably be worth your effort to check out the Office Developer
Center for Excel, which has many resources to help out.
http://msdn.microsoft.com/office/understanding/excel/default.aspx
In particular, look at the Excel articles Ken Getz wrote arount the time
VSTO was being released. Many of the coding techniques are the same whether
you are using VSTO or PIAs. Here's a real good one to read:
http://msdn.microsoft.com/office/un...brary/en-us/odc_vsto2003_ta/html/excelobj.asp
Go straight to the section on "The Range Object" and it will give you some
examples that pertain to this particular question.
Joe
G
Guest
Hi Joe,
Currently, I'm doing my development with visual c# .NET and Microsoft Excel
2003 (Office 2003). Ideally, I need to be able to make my code compatible
with prior versions of excel, at least up to Excel 2000.
(1) What compatibilty issues do I need to consider during development?
(2) I currently have PIA's for Excel 2003 installed. How do these PIA's
work with prior versions of excel?
Thanks.
Mansi
Currently, I'm doing my development with visual c# .NET and Microsoft Excel
2003 (Office 2003). Ideally, I need to be able to make my code compatible
with prior versions of excel, at least up to Excel 2000.
(1) What compatibilty issues do I need to consider during development?
(2) I currently have PIA's for Excel 2003 installed. How do these PIA's
work with prior versions of excel?
Thanks.
Mansi
J
Joe Mayo
Hi Mansi,
I've never done it. I think there may be compatibility problems because of
different versions of Excel requiring different PIAs/Interop Assemblies.
Here is where you could start looking:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/dplywithnet.asp
Joe
I've never done it. I think there may be compatibility problems because of
different versions of Excel requiring different PIAs/Interop Assemblies.
Here is where you could start looking:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/dplywithnet.asp
Joe
G
Guest
Hi Joe,
Thanks for that link.
I came across another article (Microsoft Knowledge Base Article - 244167:
Writing Automation Clients for Multiple Office Versions). I still need to
read this more carefully, but just to summarize, the article recommends that
the following:
"if you are developing an Automation client that you intend to work with
multiple versions of an Office application, you should: (1) Reference the
type library of the earliest version of the Office application you intend to
Automate. -or- (2) Use late binding.
I need to get excel automation working for versions 2000, 2002, and 2003.
So I'm assuming one path that I could take is to uninstall excel 2003 and
install excel version 2000 and reference the 2000 type library in my code.
But my next concern is that there are no PIA's available for excel 2000. So
does this mean that mean I'll have to create my own interop assembly? I
remember in one of your earlier posts, you did not recommend creating my own
interop assembly because "a whole new world of problems opens up". Is there
a way to avoid creating my own interop assembly?
Thanks for all the help.
Mansi
Thanks for that link.
I came across another article (Microsoft Knowledge Base Article - 244167:
Writing Automation Clients for Multiple Office Versions). I still need to
read this more carefully, but just to summarize, the article recommends that
the following:
"if you are developing an Automation client that you intend to work with
multiple versions of an Office application, you should: (1) Reference the
type library of the earliest version of the Office application you intend to
Automate. -or- (2) Use late binding.
I need to get excel automation working for versions 2000, 2002, and 2003.
So I'm assuming one path that I could take is to uninstall excel 2003 and
install excel version 2000 and reference the 2000 type library in my code.
But my next concern is that there are no PIA's available for excel 2000. So
does this mean that mean I'll have to create my own interop assembly? I
remember in one of your earlier posts, you did not recommend creating my own
interop assembly because "a whole new world of problems opens up". Is there
a way to avoid creating my own interop assembly?
Thanks for all the help.
Mansi
J
Joe Mayo
Hi Mansi,
In this case, you don't have a choice. You must create your own interop
assembly for Excel 2000.
Thanks for the KB ID. You may have see this already, but I poked around and
found one that shows you how to do the late binding in C#:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;302902
Joe
In this case, you don't have a choice. You must create your own interop
assembly for Excel 2000.
Thanks for the KB ID. You may have see this already, but I poked around and
found one that shows you how to do the late binding in C#:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;302902
Joe
G
Guest
Hi Joe,
To be honest, I'm a little confused because this is the first time I'm
working with interop assemblies. But here's my plan:
(1) Uninstall excel 2003 and install excel 2000. I'm assuming the uninstall
will remove all files related to excel 2003. Actually, what about the PIA's?
How do I remove these from the gac?
(2) Next, create my own interop assembly using "TlbImp Excel9.olb
Excel.dll". I got this command from one of the other posts. I'm assuming
that this is the correct way to create an interop assembly. Is this true? Or
do I reference the Excel9.olb file in my code and let the compiler create the
interop assembly for me?
I'm assuming that if I do my development with excel 2000 and reference the
interop assembly I created, it should be compatible with later versions of
excel. Also, does this mean that I can use early binding (and not have to
worry about using late binding)?
Thanks again for all the help.
Mansi
To be honest, I'm a little confused because this is the first time I'm
working with interop assemblies. But here's my plan:
(1) Uninstall excel 2003 and install excel 2000. I'm assuming the uninstall
will remove all files related to excel 2003. Actually, what about the PIA's?
How do I remove these from the gac?
(2) Next, create my own interop assembly using "TlbImp Excel9.olb
Excel.dll". I got this command from one of the other posts. I'm assuming
that this is the correct way to create an interop assembly. Is this true? Or
do I reference the Excel9.olb file in my code and let the compiler create the
interop assembly for me?
I'm assuming that if I do my development with excel 2000 and reference the
interop assembly I created, it should be compatible with later versions of
excel. Also, does this mean that I can use early binding (and not have to
worry about using late binding)?
Thanks again for all the help.
Mansi