Call a VB.Net App from an Excel Macro

[Jim Bob]

Dear Anyone:

I am building a Report Writer. I need to call an VB.Net App from a Cell
in an Excel sheet.

For instance, Ctl-d would ball a VBA Macro in excel that would in turn
call my vb.net app, provide the necessary info and return to the Excel
sheet.

How can this be done?

jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

You'll need to register the VB .NET app. for COM InterOp, which generates a
COM Callable Wrapper for it. Then, you Excel app. can call that.

Are you sure you want/need to use .NET to solve your problem? The code can
get much more complicated and because of the COM / .NET architectures,
performance may suffer.

http://edn.embarcadero.com/article/32754
http://blogs.msdn.com/jigarme/archive/2008/04/28/how-to-register-net-assembly-for-com-interop.aspx

-Scott

Post by Jim Bob
I am building a Report Writer. I need to call an VB.Net App from a Cell
in an Excel sheet.
For instance, Ctl-d would ball a VBA Macro in excel that would in turn
call my vb.net app, provide the necessary info and return to the Excel
sheet.
How can this be done?
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[f***@gemboxsoftware.com]

Using Excel Automation is most common way to do it but it has many
issues and often results with application not working on somebody
elses machine. I advice you to use GemBox.Spreadsheet component. It's
easy to use and has free version which you can try on smaller files.
http://www.gemboxsoftware.com/GBSpreadsheet.htm

[Scott M.]

If it's done properly, it will work every time. Going to a 3rd party
component will only make the process more complicated.

-Scott

Post by f***@gemboxsoftware.com
Using Excel Automation is most common way to do it but it has many
issues and often results with application not working on somebody
elses machine. I advice you to use GemBox.Spreadsheet component. It's
easy to use and has free version which you can try on smaller files.
http://www.gemboxsoftware.com/GBSpreadsheet.htm

[f***@gemboxsoftware.com]

If it's done properly, it will work every time.  Going to a 3rd party
component will only make the process more complicated.
-Scott

It is not question of being done right or wrong. It is about
compatibility with Excel versions and speed of transfering data
between Excel and .NET.

[Scott M.]

It absolutely is a matter of it being done right. One of the biggest
problems that people make with COM Callable Wrappers (CCW's) in .NET is not
properly releasing the COM objects before the .NET objects go out of scope.
This results in COM applicaitons that persist in memory after the .NET
application is shut down. With Excel, in particluar, this is a problem
because many of the Excel Object Model object methods return other objects,
which have to be explicitly destroyed. Using a 3rd party component isn't
going to change any of that, nor is it going to speed up any data transfer
or provide any additional compatibility with Excel that isn't already
available with the Office Primary InterOp Assemblies (PIA's).

I stand by my statement that if you do COM InterOp with Excel properly (ie.
properly handling all the COM object references that are created and
releasing them accordingly), you won't have any problems.

-Scott

If it's done properly, it will work every time. Going to a 3rd party
component will only make the process more complicated.
-Scott

It is not question of being done right or wrong. It is about
compatibility with Excel versions and speed of transfering data
between Excel and .NET.

[Scott M.]

Oops, mean Runtime Callable Wrappers (RCW's) for .NET to COM. It's COM
Callable Wrappers (CCW's) for COM to .NET.

-Scott

Post by Scott M.
It absolutely is a matter of it being done right. One of the biggest
problems that people make with COM Callable Wrappers (CCW's) in .NET is
not properly releasing the COM objects before the .NET objects go out of
scope. This results in COM applicaitons that persist in memory after the
.NET application is shut down. With Excel, in particluar, this is a
problem because many of the Excel Object Model object methods return other
objects, which have to be explicitly destroyed. Using a 3rd party
component isn't going to change any of that, nor is it going to speed up
any data transfer or provide any additional compatibility with Excel that
isn't already available with the Office Primary InterOp Assemblies
(PIA's).
I stand by my statement that if you do COM InterOp with Excel properly
(ie. properly handling all the COM object references that are created and
releasing them accordingly), you won't have any problems.
-Scott

If it's done properly, it will work every time. Going to a 3rd party
component will only make the process more complicated.
-Scott

It is not question of being done right or wrong. It is about
compatibility with Excel versions and speed of transfering data
between Excel and .NET.

[John Brock]

I also need to call an .NET app from Excel, and so far it's looking
kind of scary. I'm not expecting anyone to walk me all the way
through it, but I was hoping someone could reassure me that what
I am trying to do is doable (and hopefully not impossibly hard).

What I want is to give our customers the ability, from their own
Excel VBA code, to instantiate a .NET object that we provide, and
call the object's methods, passing data back and forth. That's
it! The .NET object will not modify the Excel worksheets or UI in
any way; it will do things outside of Excel, and then tell the VBA
code what it did. Basically I think I am talking about an Excel
add-in of some sort, but the details just aren't clear to me.

The important thing is that this isn't for in-house use, it's to
give to customers. So setup should be as simple as possible
(something a user without admin privileges can install as easily
as a regular Excel add-in). And it needs to work on as wide a
range of machine configurations as possible. All we want to assume
is the .NET Framework 2.0 or higher, and some version of Excel.
We don't want any 3rd party software involved. We don't want to
put anything in the GAC (the application directory is fine). We
don't want to assume that there are PIAs on the user's machine.
(.NET programs that don't use PIAs can still open and manipulate
Excel files through late binding, and I am hoping something like
that will be possible with .NET add-ins). What we really want is
for our customer to unzip a directory, click on something to install
the add-in, and go!

Finally, if possible I'd like to write this using VB 2008 Express.
I can go to Pro if necessary, but if I can stick with Express that
would be better. (I may eventually need to hand the project off
to someone who doesn't have Pro).

Anyway, the project is still in a preliminary stage, but I've been
reading about application add-ins and VSTO and whatnot for a while.
The thing is, it all seems to be addressed much higher than the
"Hello World" type of functionality I'm after. So what I'd really
like is to know, going into this, is whether what I want to do is
doable as described above, and if not where the roadblocks are,
and what's required to get around them. Any comments or links will
be most gratefully appreciated!

Post by Scott M.
If it's done properly, it will work every time. Going to a 3rd party
component will only make the process more complicated.
-Scott

Post by f***@gemboxsoftware.com
Using Excel Automation is most common way to do it but it has many
issues and often results with application not working on somebody
elses machine. I advice you to use GemBox.Spreadsheet component. It's
easy to use and has free version which you can try on smaller files.
http://www.gemboxsoftware.com/GBSpreadsheet.htm

[Jason Keats]

Post by John Brock
I also need to call an .NET app from Excel, and so far it's looking
kind of scary. I'm not expecting anyone to walk me all the way
through it, but I was hoping someone could reassure me that what
I am trying to do is doable (and hopefully not impossibly hard).
What I want is to give our customers the ability, from their own
Excel VBA code, to instantiate a .NET object that we provide, and
call the object's methods, passing data back and forth. That's
it! The .NET object will not modify the Excel worksheets or UI in
any way; it will do things outside of Excel, and then tell the VBA
code what it did. Basically I think I am talking about an Excel
add-in of some sort, but the details just aren't clear to me.
The important thing is that this isn't for in-house use, it's to
give to customers. So setup should be as simple as possible
(something a user without admin privileges can install as easily
as a regular Excel add-in). And it needs to work on as wide a
range of machine configurations as possible. All we want to assume
is the .NET Framework 2.0 or higher, and some version of Excel.
We don't want any 3rd party software involved. We don't want to
put anything in the GAC (the application directory is fine). We
don't want to assume that there are PIAs on the user's machine.
(.NET programs that don't use PIAs can still open and manipulate
Excel files through late binding, and I am hoping something like
that will be possible with .NET add-ins). What we really want is
for our customer to unzip a directory, click on something to install
the add-in, and go!
Finally, if possible I'd like to write this using VB 2008 Express.
I can go to Pro if necessary, but if I can stick with Express that
would be better. (I may eventually need to hand the project off
to someone who doesn't have Pro).
Anyway, the project is still in a preliminary stage, but I've been
reading about application add-ins and VSTO and whatnot for a while.
The thing is, it all seems to be addressed much higher than the
"Hello World" type of functionality I'm after. So what I'd really
like is to know, going into this, is whether what I want to do is
doable as described above, and if not where the roadblocks are,
and what's required to get around them. Any comments or links will
be most gratefully appreciated!

AFAIK, VBA from Excel can only connect to COM objects.

You will need to create a COM callable wrapper for your .NET application
in order to enable VBA to use it.

Here are some links that may help:
http://www.codeproject.com/KB/COM/nettocom.aspx
http://support.microsoft.com/Default.aspx?kbid=817248

HTH

[Jim Bob]

Dear Anyone:

I have my vb.net app that creates an Excel Workbook by using:

oWB =
oApp.Workbooks.Open("G:\VisualStudio\ReportWriter\GLReportWriter\bin\MyB
ook1.xlt")
Dim oWs As Excel.Worksheet = oWB.Sheets(1)
oRng = oWs.Range("D26")
oRng.Formula = "=sum(D2:D25)"

' Your Code Goes Here
' Your Code Goes Here
' Your Code Goes Here
' Your Code Goes Here
oApp.Visible = True
oWb.Activate()
oWs.Activate()

How can I get the Row and Column Selected in the Excel WS to be placed
in a textbox in my vb.net app?

jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

txtSomething.Text = Range("A1").value

or, if the row and column are identified via indexes:

txtSomething.Text = Cells(RowIndex, ColumnIndex).value

Remember that when you call various Excel Object methods, they return
references to objects (a range, a worksheet, etc.) and because you are
accessing these object from .NET, you must capture their references and
remember to explicitly destroy them (set them equal to nothing) and then
remember to "disconnect" your .NET applicaiton's reference as well via:

Marshal.ReleaseComObject(comReference)

If you don't follow this procedure, Excel.exe will still be running even
after you .NET applicaiton ends (even if you call Applicaiton.Quit).

-Scott

Post by Jim Bob
oWB =
oApp.Workbooks.Open("G:\VisualStudio\ReportWriter\GLReportWriter\bin\MyB
ook1.xlt")
Dim oWs As Excel.Worksheet = oWB.Sheets(1)
oRng = oWs.Range("D26")
oRng.Formula = "=sum(D2:D25)"
' Your Code Goes Here
' Your Code Goes Here
' Your Code Goes Here
' Your Code Goes Here
oApp.Visible = True
oWb.Activate()
oWs.Activate()
How can I get the Row and Column Selected in the Excel WS to be placed
in a textbox in my vb.net app?
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[Jim Bob]

Scott:

I must be missing something. If I go to my Excel sheet created by my vb
app, select a certain cell, this is not "seen" by my app. I can go one
way (to Excel) by setting a range and a range value, but I cannot go the
other way (what row and column is the active cell.



jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

Please show the code that you are using to populate a cell in Excel.

-Scott

Post by Jim Bob
I must be missing something. If I go to my Excel sheet created by my vb
app, select a certain cell, this is not "seen" by my app. I can go one
way (to Excel) by setting a range and a range value, but I cannot go the
other way (what row and column is the active cell.
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[Jim Bob]

ows = oWB.Sheets(1)
oRng = oWs.Range("D26")
oRng.Formula = "=sum(D2:D25)"

Of Course this is after the Excel App was created in another Sub

oWB =
oApp.Workbooks.Open("G:\VisualStudio\ReportWriter\GLReportWriter\bin\MyB
ook1.xlt")

jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

So, you just assign your textbox to oRng:

txtYourTextBox.Text = oRng.value

-Scott

Post by Jim Bob
ows = oWB.Sheets(1)
oRng = oWs.Range("D26")
oRng.Formula = "=sum(D2:D25)"
Of Course this is after the Excel App was created in another Sub
oWB =
oApp.Workbooks.Open("G:\VisualStudio\ReportWriter\GLReportWriter\bin\MyB
ook1.xlt")
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[Jim Bob]

Scatt:

I realize I can get the value from the oRng. How can I update the oRng
values inside of Excel?

What I am wanting to do is use Excel as a Report writing "Tool". The
value of Row, Cell etc is taken from Excel and my .net app updates a SQL
database with this info.
My Excel sheet then becomes a "Template" xlt .

On report creation, I open this Template, get the row, column and data
from SQL and create the report.


jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

I'm sorry, I don't think I understand your question.

If you want to extract an Excel cell value and use it in your .NET program
you'd write:

txtYourTextBox.Text = oRng.value

You could put the value in a .NET textbox, as you asked about earlier, or
you could put it into a SQL Server database, you can do anything with it.

If you want to place a value into an Excel cell, you'd just reverse the
expression:

oRng.value=txtYourTextBox.Text

Am I missing something?

-Scott

Post by Jim Bob
I realize I can get the value from the oRng. How can I update the oRng
values inside of Excel?
What I am wanting to do is use Excel as a Report writing "Tool". The
value of Row, Cell etc is taken from Excel and my .net app updates a SQL
database with this info.
My Excel sheet then becomes a "Template" xlt .
On report creation, I open this Template, get the row, column and data
from SQL and create the report.
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[Jim Bob]

Scott:

The way you describe it, I must stay in .net. If I want to change to a
different Cell in Excel, I must do that by changing the oRng in .net.

What I am looking for is a way (inside of Excel) that when I click to a
cell in Excel, my .net app knows the Row/Column location. Excel needs
to communicate with the parent .net app.



jwc

*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

Cells in Excel don't have a "click" ever per se, but you could have a button
on the spreadsheet that a user could click to "update" the SQL database. In
the button's click event handler, you'd simply take the value of the
activecell and send it to the db, you don't need .NET to do this and unless
you have a compelling reason to want to use .NET, you probably shouldn't as
the InterOp layer will slow down the application.

You could connect to the SQL db using VBA directly.

-Scott

Post by Jim Bob
The way you describe it, I must stay in .net. If I want to change to a
different Cell in Excel, I must do that by changing the oRng in .net.
What I am looking for is a way (inside of Excel) that when I click to a
cell in Excel, my .net app knows the Row/Column location. Excel needs
to communicate with the parent .net app.
jwc
*** Sent via Developersdex http://www.developersdex.com ***

[Jim Bob]

Scott:

The way you describe it, I must stay in .net. If I want to change to a
different Cell in Excel, I must do that by changing the oRng in .net.

What I am looking for is a way (inside of Excel) that when I click to a
cell in Excel, my .net app knows the Row/Column location. Excel needs
to communicate with the parent .net app.



jwc

*** Sent via Developersdex http://www.developersdex.com ***