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 17 6281
Hi Mansi,
Here's a collection of relevant information on MSDN: http://msdn.microsoft.com/office/und...l/default.aspx
Joe
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:A6**********************************@microsof t.com... 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
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
"Joe Mayo" wrote: Hi Mansi,
Here's a collection of relevant information on MSDN:
http://msdn.microsoft.com/office/und...l/default.aspx
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:A6**********************************@microsof t.com... 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
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
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:E2**********************************@microsof t.com... 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
"Joe Mayo" wrote:
Hi Mansi,
Here's a collection of relevant information on MSDN:
http://msdn.microsoft.com/office/und...l/default.aspx
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:A6**********************************@microsof t.com... 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
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
"Joe Mayo" wrote: 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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:E2**********************************@microsof t.com... 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
"Joe Mayo" wrote:
Hi Mansi,
Here's a collection of relevant information on MSDN:
http://msdn.microsoft.com/office/und...l/default.aspx
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:A6**********************************@microsof t.com... > 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 >
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
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:0F**********************************@microsof t.com... 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?
I followed the steps listed on the MSDN site to install the PIAs for Office
2003. I'm assuming they were installed successfully.
In my c# .NET project, I added a reference to "Microsoft Access 11.0 Object
Library". Also, I added the following lines to the end of the list of using
directives: using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
How do I confirm Visual Studio .NET is actually using the PIAs and not
generating its own interop assembly for the project?
Thanks for all the help.
Mansi
"Joe Mayo" wrote: 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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
Hi Mansi,
Right-click on the reference under the References folder in Solution
Explorer and select Properties. The path should lead to the
%windir%\assembly\gac\.... directory. If you are pointing to something
local, that local directory will show up.
I noticed that you referenced the "Access" PIA, but are writing an alias for
Excel. It seems like it shouldn't work. IIRC, the PIA namespace is not the
same as a VS.NET auto-generated interop assembly. So, if you are still able
to reference Microsoft.Office.Interop.Excel, then it seems like you still
have the old auto-generated interop assembly in your references list. You
should delete all the old interop assemblies.
I remember having this problem back when I migrated from interop assemblies
to PIAs. It was very difficult to get the interop assemblies out of the
reference list. I removed all of the interop assemblies and PIA references
from the project, went to the file system and deleted all of the
auto-generated interop assemblies, and then re-added PIAs. Remember that
generating interop assemblies also generates interop assemblies for all of
it's dependent type libraries. So, in addition to the Excel interop
assembly, you get Microsoft.Office.Core, stdole, and VBIDE. When you do
this, look at the Path property, described above, to verify that you are
referencing the PIA in the GAC.
Joe
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:73**********************************@microsof t.com... I followed the steps listed on the MSDN site to install the PIAs for
Office 2003. I'm assuming they were installed successfully.
In my c# .NET project, I added a reference to "Microsoft Access 11.0
Object Library". Also, I added the following lines to the end of the list of
using directives: using Excel = Microsoft.Office.Interop.Excel; using System.Reflection;
How do I confirm Visual Studio .NET is actually using the PIAs and not generating its own interop assembly for the project?
Thanks for all the help.
Mansi
"Joe Mayo" wrote:
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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
Hi Joe,
I checked the path under properties and confirmed that it references the GAC
directory. (i.e. For Excel - path refers to
c:\Windows\assembly\GAC\Microsoft.Office.Interop.E xcel\...\Microsoft.Office.Interop.Excel.dll).
In my previous email, I referred to the wrong object library. I meant to
say that I added a reference to "Microsoft Excel 11.0 Object Library".
Under references, in addition to the Excel interop assembly, I see
Microsoft.Office.Core and VBIDE, but I don't see stdole. Is that a problem?
Thanks for all the help.
Mansi
"Joe Mayo" wrote: Hi Mansi,
Right-click on the reference under the References folder in Solution Explorer and select Properties. The path should lead to the %windir%\assembly\gac\.... directory. If you are pointing to something local, that local directory will show up.
I noticed that you referenced the "Access" PIA, but are writing an alias for Excel. It seems like it shouldn't work. IIRC, the PIA namespace is not the same as a VS.NET auto-generated interop assembly. So, if you are still able to reference Microsoft.Office.Interop.Excel, then it seems like you still have the old auto-generated interop assembly in your references list. You should delete all the old interop assemblies.
I remember having this problem back when I migrated from interop assemblies to PIAs. It was very difficult to get the interop assemblies out of the reference list. I removed all of the interop assemblies and PIA references from the project, went to the file system and deleted all of the auto-generated interop assemblies, and then re-added PIAs. Remember that generating interop assemblies also generates interop assemblies for all of it's dependent type libraries. So, in addition to the Excel interop assembly, you get Microsoft.Office.Core, stdole, and VBIDE. When you do this, look at the Path property, described above, to verify that you are referencing the PIA in the GAC.
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:73**********************************@microsof t.com... I followed the steps listed on the MSDN site to install the PIAs for Office 2003. I'm assuming they were installed successfully.
In my c# .NET project, I added a reference to "Microsoft Access 11.0 Object Library". Also, I added the following lines to the end of the list of using directives: using Excel = Microsoft.Office.Interop.Excel; using System.Reflection;
How do I confirm Visual Studio .NET is actually using the PIAs and not generating its own interop assembly for the project?
Thanks for all the help.
Mansi
"Joe Mayo" wrote:
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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... > 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?
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:1D**********************************@microsof t.com... Hi Joe,
I checked the path under properties and confirmed that it references the
GAC directory. (i.e. For Excel - path refers to
c:\Windows\assembly\GAC\Microsoft.Office.Interop.E xcel\...\Microsoft.Office.
Interop.Excel.dll).
Good deal.
In my previous email, I referred to the wrong object library. I meant
to say that I added a reference to "Microsoft Excel 11.0 Object Library".
That's good. I just wanted to check because it would have been very
confusing for you otherwise. ;)
Under references, in addition to the Excel interop assembly, I see Microsoft.Office.Core and VBIDE, but I don't see stdole. Is that a
problem?
No problem. The stdole reference gets pulled in any time you create an
auto-generated reference with a type library in VS.NET. In the case of the
PIAs, it is not pulled into your reference list.
Joe
-- http://www.csharp-station.com
I'm now able to successfully open up and write to an excel workbook.
Thanks for all the help :)
Mansi
"Joe Mayo" wrote: "Mansi" <Ma***@discussions.microsoft.com> wrote in message news:1D**********************************@microsof t.com... Hi Joe,
I checked the path under properties and confirmed that it references the GAC directory. (i.e. For Excel - path refers to c:\Windows\assembly\GAC\Microsoft.Office.Interop.E xcel\...\Microsoft.Office. Interop.Excel.dll).
Good deal.
In my previous email, I referred to the wrong object library. I meant to say that I added a reference to "Microsoft Excel 11.0 Object Library".
That's good. I just wanted to check because it would have been very confusing for you otherwise. ;)
Under references, in addition to the Excel interop assembly, I see Microsoft.Office.Core and VBIDE, but I don't see stdole. Is that a problem?
No problem. The stdole reference gets pulled in any time you create an auto-generated reference with a type library in VS.NET. In the case of the PIAs, it is not pulled into your reference list.
Joe -- http://www.csharp-station.com
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
"Joe Mayo" wrote: 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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
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/und...l/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/und...l/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
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:53**********************************@microsof t.com... 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
"Joe Mayo" wrote:
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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
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
"Joe Mayo" wrote: 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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
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/de...plywithnet.asp
Joe
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:88**********************************@microsof t.com... 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
"Joe Mayo" wrote:
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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... 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?
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
"Joe Mayo" wrote: 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/de...plywithnet.asp
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:88**********************************@microsof t.com... 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
"Joe Mayo" wrote:
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 -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:0F**********************************@microsof t.com... > 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?
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...b;EN-US;302902
Joe
-- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message
news:36**********************************@microsof t.com... 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?
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
"Joe Mayo" wrote: 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...b;EN-US;302902
Joe -- http://www.csharp-station.com
"Mansi" <Ma***@discussions.microsoft.com> wrote in message news:36**********************************@microsof t.com... 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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ange T |
last post by:
Hi there,
I'm having pain with the VB behind an Access form. The form is used to
create reports in Excel based on the details entered in the form. This
has always worked without error on my...
|
by: jeffgeorge |
last post by:
I'm currently exporting a form to Excel. Because there are controls
and totals in the header, I first have a button for users to convert
to a datasheet. Then I use the automated quick office...
|
by: taylor.bryant |
last post by:
I am running:
Win XP SP2
Excel 2002, Access 2002 (Office XP SP3)
Using Visual Basic (not VB.NET)
At one point (prior to XP SP2?!? - I can't pin it down), this did
not happen and I was easily...
|
by: cybertof |
last post by:
Hello,
Is there a way to connect (through automation) a c# application to a
running Excel 2003 instance on a specific workbook ?
In the past, i used to use GetObject(...) function in VB6.
...
|
by: D. Shane Fowlkes |
last post by:
This most likely belongs in another forum but I thought I'd start here. I
have a COM Object written in VB6. The DLL will access MS Excel and use it's
Object Library to write a customized report...
|
by: elziko |
last post by:
I'm using late binding (I must) to automate Excel.
My code opens Excel after createing and poulating some sheets. My problem is
that when the user finally decides to close Excel its process is...
|
by: Carlos Magalhaes |
last post by:
Hey All,
I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.
I can run a formula and insert the formula...
|
by: Mitchell Vincent |
last post by:
Does anyone have some good examples of Excel automation with (VB).NET?
I have some Excel spreadsheets that a customer needs parsed out but I've
never tried to use Excel programatically before!
...
|
by: a.theil |
last post by:
Please help!
I need a simple excel automation, just 2 write some files into excel.
I do:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |