Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app
Excel needs to work with my vb.net app Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Go Back  Xtreme Visual Basic Talk > > > Excel needs to work with my vb.net app


Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2010, 02:28 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default Excel needs to work with my vb.net app


I have a vb.net app that retreives data from a MS SQL 2005 database. I have it creating an Excel 2003 workbook.

In a cell of a Worksheet, I want to "Call" or make active my vb.net App via a key combination (Alt->V) and have the workbook/worksheet pass the Row number / Column to the vb.net app that will get the data and post back to this Cell. This combination (vb.net and Excel) is my report builder app.


So I want to go back and forth between vb.net and Excel -- passing the needed data from each.

How can this be done?

Jim
Reply With Quote
  #2  
Old 03-29-2010, 03:29 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

This sounds to me that you should create a managed COM add-in for Excel. The functionality that you describe could then be either:

(1) Controlled 100% from within the managed COM add-in (that is, the managed COM add-in would *be* your application), or

(2) Have most of the functionality controlled within a stand-alone EXE, while the managed COM add-in enables communication between your add-in and your EXE.

Version 1, having the functionality fully-contained within a managed COM add-in would be a lot easier. To learn about Managed COM add-ins, see How To Build an Office COM Add-in by Using Visual Basic .NET.

If you want to do version 2, it's pretty advanced, but can be done. In version 2, you would need to think about whether it is Excel that creates the VB.NET EXE when needed, or if it is the VB.NET EXE that creates an Excel application instance when needed. (You could enable both, if you are ambitious.)

In general, version 2 falls under the heading of inter-process communication so anything could work here, including remoting or WCF. Managed COM add-ins, however, enables this kind of communication via the [url=ComAddin.Object property[/url]. For an advanced discussion on this see:

(1) Calling Managed Add-In method from Automation client
(2) Moving from an Excel xla add-in to a C# add-in
(3) How to cast COMAddIn.Object in C#?

I would read these articles, and then make a decision based on the functionality you need.

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 03-30-2010, 11:20 AM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default Need help with Step 1

Mike:

I am attempting Step one and I have run into problems. It is most likely a reference problem.

It tells me in statements such as:

Dim oCommandBars As CommandBars <--- Not Defined
Dim oStandardBar As CommandBar <--- Not Defined
With MyButton
.Caption = "My Custom Button"
.Style = MsoButtonStyle.msoButtonCaption <-- Not Declared

---In this one, it tells me "CommandBarButton is ambiguous in Microsoft.Office.Core"

Private Sub MyButton_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles MyButton.Click
MsgBox("Our CommandBar button was pressed!")
End Sub

Can you tell me what to look for?

Jim
Reply With Quote
  #4  
Old 03-30-2010, 11:46 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Jim,

Quote:
Originally Posted by Jimc1611 View Post
Mike:

I am attempting Step one and I have run into problems. It is most likely a reference problem.

It tells me in statements such as:

Dim oCommandBars As CommandBars <--- Not Defined
Dim oStandardBar As CommandBar <--- Not Defined
With MyButton
.Caption = "My Custom Button"
.Style = MsoButtonStyle.msoButtonCaption <-- Not Declared
You cannot just use 'CommandBars' as you could in VBA, you must fully qualify it as 'Microsoft.Office.Core.CommandBars'.

To help with this you can put

Code:
Imports Office = Microsoft.Office.Core
At the top of your document and then you can use the alias 'Office' to represent 'Microsoft.Office.Core'. Once you do this, you can use 'Office.CommandBars'.

Similarly, it's usually a good idea to also put
Code:
Imports Excel = Microsoft.Office.Interop.Excel
at the top of your document to make it easier for you to access types from the Excel object model.

Quote:
---In this one, it tells me "CommandBarButton is ambiguous in Microsoft.Office.Core"

Private Sub MyButton_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles MyButton.Click
MsgBox("Our CommandBar button was pressed!")
End Sub
On this one I'm not so sure. For some reason it thinks that 'Microsoft.Office.Core.CommandBarButton' is ambiguous, but it looks explicit to me. Check your references within your Solution explorer. Do you see 'Microsoft.Office.Core' listed twice for some reason?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 03-30-2010, 12:14 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default That did it.

Mike:

The Extensibility Wizard automatically put a reference to "Office". I also had added Microsoft.Office.Core. I removed "Office" and it compiled. Thanks.

I am in another world. I already have an app that calls Excel. I want Excel, when I am in a certain Cell, to go to my app and retreive a specific piece of Data. The App will need the Row number and Column Number from Excel and record the data selected (Account No) and the app will record this info in a SQL database, This is the "Create Report" phase of the APP.

After the "Create Report" (a one time effort), a second function of the app is to take the data accumulated in the "Create Report" phase, get the appropiate data and post it to the Excel Sheet Row No / Column No. Thsi is the "Run Report" phase.

I have the "Run Report" done (VB.net to Excel). I need help with the "Create Report" (Excel to VB.Net and Back to Excel). How can this Addin call my app (The Command Button), give my app the Row/Column, then in the App, once the data is selected, return to the Excel. I only asking the framework not for you to write the code.

Thanks


Jim
Reply With Quote
  #6  
Old 03-30-2010, 12:39 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Jimc1611 View Post
The Extensibility Wizard automatically put a reference to "Office". I also had added Microsoft.Office.Core. I removed "Office" and it compiled. Thanks.
Nice.

Quote:
I am in another world. I already have an app that calls Excel. ...
I know, this is hurts. The problem is that interprocess communication is difficult. If I were better at remoting or WCF, I could offer those approaches as solutions. However, those approaches would be harder, not easier.

What is tough about the approach that you are describing is that you want (1) your stand-alone application to be able to call and control Excel, but also have (2) a command within Excel that calls back to your stand-alone application. It is the second part of this that is difficult.

Two ideas for you:

(1) Keep going with creating a managed COM add-in. Once you get it loading, you'll have two options:

(a) Move all your functionality from your stand-alone application to the Excel add-in. Not sure if this is viable for you, however.

(b) Enable bidirectional communication between Excel and your add-in via the ComAddins.Object property of you managed COM add-in. I can walk you through this part. It's advanced, but it really is doable.

(2) Forget about the bidirectional communication and simply have all the functionality within your stand-alone EXE only. This would be *much* easier and would require no managed COM add-in. For example, instead of the user selecting a cell or range within Excel and then clicking a button within excel that calls out to your stand alone EXE, have the user select a cell or range within Excel and then click a button within your stand-alone EXE. I realize that this is less convenient for the user, but would be much easier for you.

If you want to have the bidirectional communication, though, we really will have to get the managed COM add-in going, which I can definitely get you going 100%, if you stick with it.

So, you'll have to make a choice as to strategic direction...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 03-30-2010, 02:44 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default Thanks

I googled remoting and came across this.

What do you think of the software on this site as it relates to me.


http://www.add-in-express.com/


Jim
Reply With Quote
  #8  
Old 03-30-2010, 03:37 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

It is supposed to be an excellent development platform. If it has a 30-day trial or the like, you could definitely check it out.

I don't think it helps with the inter-process communication aspect though. So if you were looking to go with version (1a) that I listed in Post #6, above, this could be a nice tool. However, if you were going to keep most of the functionality within a stand-alone EXE and mostly need some communication between the two, then I don't think that this add-in would help much. It might be a little easier to set up, but a basic managed COM add-in is not hard (and again, I can definitely help get you there).

So I would consider kicking it around if they have a free trial. It's best to decide these things once you know the capabilities of the platform. But then you have to decide if you want to move all your functionality to within Excel, in which case Add-In Express could be very nice, or maintain a separate EXE that communicates with Excel, in which case any advantage of Add-In Express would likely be more limited.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 03-30-2010, 04:12 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default My preferences

Mike:

The more I look at it, the more I want the .Net to be the Master and the Excel to be the slave. With that said, all my .net needs from excel is the Row/Column number and the ability to go back to the master.

How dificult is some vba method in excel communicating with a function/sub in .net?
Reply With Quote
  #10  
Old 03-30-2010, 05:18 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Huh, that's not a bad idea. It would be easier to set up than a managed COM add-in and easier to deploy. It would avoid a lot of headaches, actually.

The key is that you would need to create a COM-visible interface and class that you can pass into your VBA method. Lets' call it the 'ISubscriber' interface, and on it have the 'SomethingHappenedCallback' method. Your .NET EXE would need to use 'Excel.Application.Run' to run, say the, "AddSubscriber" VBA method, passing in a class that implements the the 'ISubscriber' interface. The VBA add-in would hold a reference to the ISubscriber passed in as a field.

Whenever the user clicked on a button, hot-key, or whatever command that occurred, then some VBA method would call the 'ISubscriber.SomethingHappenedCallback', which would achieve the result of notifying your EXE that the command occurred within Excel.

This callback method can, of course, have parameters that can convey even more information about the call, and multilpe members can be included on this same interface.

Strategically, it's pretty much the same approach as using a managed COM add-in, but it avoids a lot of setup and deployment issues. Sorry I didn't think of this earlier. (Actually, I didn't think of this at all! You did! )

If you want to go forward with this approach, I can lay it out in more detail. Just say the word...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 03-31-2010, 08:40 AM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default I'm ready

Mike:

I am ready to begin. How do I expose a common function/sub in both the vba and .net that both can "see".

Also, yesterday, I did the Com ADD-In that added a button to excel. How do I undo what this addin did. Or how can an COM addin be made to be temporary to Excel rather than permenant.

I plan to run this app in a terminal server environment with only a few users having the need for this app. I don't want every user that uses Excel to have the COm Addin functionality.
Reply With Quote
  #12  
Old 03-31-2010, 09:13 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Jim
Also, yesterday, I did the Com ADD-In that added a button to excel. How do I undo what this addin did. Or how can an COM addin be made to be temporary to Excel rather than permenant.

I plan to run this app in a terminal server environment with only a few users having the need for this app. I don't want every user that uses Excel to have the COm Addin functionality.
I would install this on a per-user basis instead of installing to the local machine. This is an option when you create the COM add-in via the Wizard. (It's also an option when being installed.)

Quote:
Originally Posted by Jim
I am ready to begin. How do I expose a common function/sub in both the vba and .net that both can "see".
You'd have to create a COM-visible interface in your .NET project. A COM-visible interface can be seen and used by VBA. Actually, you'll need a pair of interfaces, something like IServer and IClient, where, in this case, VBA is actually the server and your VB.NET exe is the client.

Let me put together a small working example and then I'll post back...

- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #13  
Old 03-31-2010, 11:24 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok Jim, here we go...

The basic strategy is that we need to create a COM-visible interface in .NET so that the VBA add-in can call back. We'll call this interface the 'IClient' interface.

We could also create another COM-visible interface called "IServer" or "IHost" or the like, so that we can call the VBA method using early binding, but for this example we'll make it easy on ourselves and use late binding via the 'Excel.Application.Run' method.

The COM-visible 'IClient' interface that we will create is very simple:

Code:
<ComVisible(True)> _
<Guid("F745B5F1-03B5-4cb9-8873-0969EF549E2C")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface IClient
    ' The VBA add-in calls the client via IClient.CallbackToClient().
    Sub CallbackToClient(ByVal message As String)
End Interface
The interface is made COM-visible via the use of the ComVisbleAttribute and GuidAttribute. The InterfaceTypeAttribute setting the interface type to be dual allows the interface to be accessed via both early and late binding; this is not strictly necessary here, but it's usually a good idea.

We then implement the IClient interface within a COM-visble 'Client' class as follows:

Code:
<ComVisible(True)> _
<Guid("440F67D0-321A-42fb-BACD-D01C21C89443")> _
<ClassInterface(ClassInterfaceType.None)> _
<ComDefaultInterface(GetType(IClient))> _
Public Class Client
    Implements IClient

    ' Implement the 'IClient.CallbackToClient()' method.
    Public Sub CallbackToClient(ByVal message As String) _
        Implements IClient.CallbackToClient

        MessageBox.Show(message)
    End Sub
End Class
Pretty much the same idea, except that here the ClassInterfaceAttribute and ComDefaultInterface attributes specify that this COM-visble Client class utilize the 'IClient' interface, and only the 'IClient' interface, as it's default interface.

Once the assembly is compiled and the registered using RegAsm, The 'Client' class will be fully COM-visible and can be utilized by any COM code, including VBA.

(Continued next post...)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #14  
Old 03-31-2010, 11:27 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok, now let's put it to use...

I'm attaching the code as a .zip file to the next post, but the basic steps to put this to work are as follows:

For starters, create a new Windows Application in Visual Studio. Add two buttons changing the programmatic names to 'btnCreateExcel' and 'btnCloseExcel' to the 'Form1' class. Then double click on each button to create the default Button.Click event handlers.

Then replace the entire code of 'Form1' with the following:
Code:
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core

Module Globals
    ' *** Change this filePath as required ***
    Friend Const filePath As String = "C:\FullPathToYourVbaAddin\"
    Friend Const vbaAddinName As String = "VbaServer.xla"
    Friend Const vbaAddinFullName As String = filePath + vbaAddinName
End Module

<ComVisible(True)> _
<Guid("F745B5F1-03B5-4cb9-8873-0969EF549E2C")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface IClient
    ' The VBA add-in calls the client via IClient.CallbackToClient().
    Sub CallbackToClient(ByVal message As String)
End Interface

<ComVisible(True)> _
<Guid("440F67D0-321A-42fb-BACD-D01C21C89443")> _
<ClassInterface(ClassInterfaceType.None)> _
<ComDefaultInterface(GetType(IClient))> _
Public Class Client
    Implements IClient

    ' Implement the 'IClient.CallbackToClient()' method.
    Public Sub CallbackToClient(ByVal message As String) _
        Implements IClient.CallbackToClient

        MessageBox.Show(message)
    End Sub
End Class

Public Class Form1
    Dim _excelApp As Excel.Application
    Dim _vbaAddin As Excel.Workbook
    Dim _client As Client ' Holds the 'Client' class that receives the callback.

    ' Creates a new Excel application, opens up the VBA add-in if required,
    ' and then logs onto the VBA add-in via the COM-visible IServer and 
    ' IClient interfaces.
    Private Sub btnCreateExcel_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles btnCreateExcel.Click

        ' Create Excel and get or create the VBA add-in.
        If Me._excelApp Is Nothing Then
            Me._excelApp = New Excel.Application
            Me._excelApp.Visible = True

            Try
                Me._vbaAddin = Me._excelApp.Workbooks(vbaAddinName)
            Catch ex As Exception
                Me._vbaAddin = Me._excelApp.Workbooks.Open(vbaAddinFullName)
            End Try
        End If

        ' Create a new 'IClient' and log into the VBA add-in.
        Me._client = New Client
        Me._excelApp.Run("AddClient", Me._client)
    End Sub


    ' Logs off of the VBA add-in, closes Excel, and performs cleanup.
    Private Sub btnCloseExcel_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles btnCloseExcel.Click

        ' Log off the VBA add-in.
        Me._excelApp.Run("RemoveClient")

        ' Cleanup and exit Excel.
        GC.Collect()
        GC.WaitForPendingFinalizers()

        Marshal.FinalReleaseComObject(Me._vbaAddin)
        Me._vbaAddin = Nothing

        Me._excelApp.Quit()
        Marshal.FinalReleaseComObject(Me._excelApp)
        Me._excelApp = Nothing
    End Sub
End Class
Note that the above includes:
Code:
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core
Therefore, you'll need to add a reference to the Microsoft Excel and Microsoft Office object libraries.

You can look through the code for 'Form1' but, basically, the 'btnCreateExcel_Click' handler creates a new 'Excel.Application', opens up the VBA add-in, and then subscribes by calling 'AddClient' via 'Excel.Application.Run', passing in a reference to a COM-visble 'Client' class that contains the 'IClient.CallbackToClient' callback method.

The 'btnCloseExcel_Click' handler does the opposite: logs off the VBA add-in by calling 'RemoveClient', and then closes Excel, and then cleans up the COM objects.

For VBA to be able to respond, however, we need to compile and then register the .NET assembly using RegAsm. This can be done using a command line, but I prefer to use a .BAT file so that I can get the command right once, save it, and then use it any number of times I want.

The .BAT file to register the .NET add-in looks as follows:
Code:
Echo Off

C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe
"C:\...\StandAloneExe\bin\Debug\StandAloneExe.exe" /codebase /tlb

Pause REM "Press any key to continue..."
The path used to the executable in the above is the path to where I had it. You would have to adjust this path to be wherever you are installing it.

The code to unregister it is similar, but uses the /u switch:
Code:
Echo Off

C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe /u
"C:\...\StandAloneExe\StandAloneExe\bin\Debug\StandAloneExe.exe"

Pause REM "Press any key to continue..."
Again, the path in the above would have to be edited to wherever you place the VB.NET assembly on your machine.

Once registered, the VBA add-in is ready to make use of it.

The VBA add-in for this example is named "VbaServer.xla" and has a reference added to the 'StandAloneExe' via Alt > Tools > References within the VBA IDE. ("StandAloneExe" is what I named the VB.NET exe, so this is how it comes through when registered.)

The code for it is as follows:
Code:
Option Explicit

Dim m_Client As StandAloneExe.Client

Public Sub AddClient(TheClient As StandAloneExe.Client)
    Set m_Client = TheClient
End Sub

Public Sub RemoveClient()
    Set m_Client = Nothing
End Sub

' How you call this method is up to you!
' Could be a Ribbon control, CommandBarControl, Worksheet.Change event, etc.
Public Sub CallbackToClient(message As String)
    If Not (m_Client Is Nothing) Then
        Call m_Client.CallbackToClient(message)
    End If
End Sub
Once the client has subscribed by calling the 'AddClient' method, any calls to the 'CallbackToClient' method will be forwarded to the client via IClient.CallbackToClient.

The last step is how to call the 'CallbackToClient' method? Well, this is up to you. It could be a Ribbon control, CommandBarControl, Worksheet.Change event, or anything else you'd want. For this example, however, I created another workbook named "Command Workbook.xls" which has a button on it and calls the 'CallbackToClient' method via 'Excel.Application.Run'.

(Continued next post...)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 03-31-2010, 11:34 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

So, so get this whole package running:

(1) Open the VB.NET windows form application and edit the path to the location of the VBA add-in within the Module holding the path constants. Then compile the VB.NET exe when done.

(2) Edit the .BAT files for registering the .NET assembly and then run the .BAT files by right-clicking on it and choosing 'Run As Administrator'. (If it quickly runs and disappears without pausing, then there is a problem and it did not register. You may need to move the .BAT file to a permitted location -- the My Documents folder may work, it does for me.)

(3) Then run the VB.NET application. Once compiled and registered you can run this either via the compiled exe or from within Visual Studio. For debugging purposes, run from within Visual Studio. Then click on the "Create Excel" button, which should open up Excel.

(4) Within Excel, open up the "Command Workbook.xls" workbook and then click on the button. If you then click back over to the VB.NET application you should see a message box waiting for you.

That's about it. I know it's a lot to digest, but look over the code and try to give it a run. Once you have it running I think you should be able to understand it well enough to adjust it for your own needs.

Let us know how it goes...

Mike
Attached Files
File Type: zip Bidirectional VBA-.NET Communication.zip (76.6 KB, 7 views)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #16  
Old 03-31-2010, 03:11 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default Now Some Tweaking

Mike:

Thank-you that worked great.... Some questions.

1. Is there a reason you did not open the workbook "Command Workbook"?
or may I do this in the same step.

2. If I have the vba code firing on a ctl -v , (I changed the interface to send the Row and Column) how can I make active my .net program from this ctl-v event?

3. When in my vb.net app, how to go back to the existing worksheet. In other words, I want to be able to toggle from vb.net to excel to vb.net.
Reply With Quote
  #17  
Old 03-31-2010, 03:48 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Jimc1611 View Post
1. Is there a reason you did not open the workbook "Command Workbook"? or may I do this in the same step.
You can do whatever you'd want, of course!

It is my guess that you won't need the "Command Workbook" at all. This was justthe easiest way for me to create a button that the user could click for this example.

My guess is that in your solution you would not use the "Command Workbook" at all, but would instead have the "Server.xla" create Ribbon controls, CommandBarButton controls, or trap hot-keys. It's up to you how you want the user to be able to initiate these commands.

Quote:
2. If I have the vba code firing on a ctl -v , (I changed the interface to send the Row and Column) how can I make active my .net program from this ctl-v event?
I would make use of the Excel.Application.OnKey callback for this. For 'ctrl + v' you would want to call something like:

Code:
Call Application.OnKey("^v", "MyMacro")
Quote:
3. When in my vb.net app, how to go back to the existing worksheet. In other words, I want to be able to toggle from vb.net to excel to vb.net.
The user should be able to click on Excel or your application as they want. If you want to *force* the focus to switch, then you'd have to make use of an API. SetFocus comes to mind.

To declare it for VBA it would look like this:
Code:
' VBA
Declare Function SetFocus Lib "user32" Alias "SetFocus" (ByVal hwnd As Long) As Long
When called from VB.NET, we have to change 'Long' to 'Integer', like this:
Code:
' VB.NET
Declare Function SetFocus Lib "user32" Alias "SetFocus" (ByVal hwnd As Integer) As Integer
Note that SetFocus requires that you know the Hwnd. For the Excel application this can be gotten via 'Excel.Application.Hwnd'. For your VB.NET exe, however, I think you'd use 'Me.Handle' from within your form.

Note that for your VB.NET exe to activate the Excel.Application instance, this is easy, since it can get the Hwnd via Excel.Application.Hwnd. For VBA to activate the VB.NET application, however, would require that your IClient interface include a 'WindowHandle As Integer' property so that the VBA server can know the Hwnd and use it when calling SetFocus.

Make sense?

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-31-2010 at 04:26 PM.
Reply With Quote
  #18  
Old 04-01-2010, 03:56 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default

Mike:

I am making progress.

I want to create a command button in the server.xla that will be loaded only when my app loads it. It will fire the other method in this server.xla.

I have only seen this done in the "How to build an Office Com ADD-in by using .net. How do I do this in my Server.xla?

Jim
Reply With Quote
  #19  
Old 04-01-2010, 05:03 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default How do I get across the Mason-Dixon Line?

Mike:

In my code I have :

Public Sub CallbackToClient(ByVal MyRow As String, ByVal MyColumn As String) _
Implements IClient.CallbackToClient
StandAloneExe.Form1.TextBox1.Text = MyRow
StandAloneExe.Form1.TextBox2.Text = MyColumn

'MessageBox.Show("Row = " & MyRow & ", MyColumn = " & MyColumn)
End Sub

I want to load Textbox1 & 2 with the current Row/Column given to me when the Sub is fired in Excel. The MyRow and MyColumn have the right information, but my textboxes remain blank on the form. How do I cross from Class Client to the Class Form1?


Jim
Reply With Quote
  #20  
Old 04-01-2010, 05:16 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

This sounds like a thread synchronization issue. Basically, a call to a user interface element like a control on a form needs to occur within the thread of the form itself.

You can make a synchronized call to the control by making use of the 'BeginInvoke' method and passing it a delegate to the method that acts on the control. See the thread Excel/MyApp Threading Issue, pls help for an example.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
 
Excel needs to work with my vb.net app
Excel needs to work with my vb.net app
 
-->