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
  #21  
Old 04-01-2010, 05:20 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
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?
Code that creates a CommandBarControl for a managed COM add-in will be almost identical for .NET. Just beware of minor differences, such as VBA requiring the use of the 'set' keyword when assigning reference types and a few other minor issues.

An alternative, by the way, is to create bidirectional communication between a managed COM add-in and your VB.NET code instead of using a VBA add-in. It's essentially the same approach as we've laid out here, and it would allow you to use .NET code within your add-in instead of VBA. Since you already have a managed COM add-in that does this, it would be very easy to take it one more step would not be hard.

Let me know if you'd want to pursue this.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #22  
Old 04-02-2010, 09:13 AM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default How to create a command button

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
  #23  
Old 04-02-2010, 12:15 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

Three possible options:

(1) Use a managed COM add-in as your "server" component instead of using VBA. It would be some work for you to convert at this point, but I could walk you through it. Once you have the managed COM add-in working, it's not that hard.

(2) Using the current VBA "Server.xla" that you have now, you can use the same code that exists in the in How To Build an Office COM Add-in by Using Visual Basic .NET, except that

(a) You would execute the code from within your VB.NET application, and

(b) You would want to set the OnAction property to the name of a macro within your VBA server. E.g., instead of .OnAction = "!<MyCOMAddin.Connect>" use .OnAction = "MyMacroInServerXla".

(3) Same approach as #2, but you could also call your code using VBA from within your 'Server.xla'. In that case you would need to:

(a) Translate the code from VB.NET to VBA, which is a fairly trivial translation. I can help if you get stuck, and

(b) Again, set the OnAction property to the name of a macro within your VBA server. E.g., instead of .OnAction = "!<MyCOMAddin.Connect>" use .OnAction = "MyMacroInServerXla".

The choice is up to you...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #24  
Old 04-05-2010, 03:59 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default I would like to see both 2 and 3

Mike:

I would like to see both 2 and 3.

I want to see 2 because I still haven't solved my thread problem and I think if I call a sub in the Server.Xla, I could get the ActiveCell.Row and ActiveCell.Column.

The action of the new Button would be to call the sub in the XLA and then return to the VB.Net.

Jim
Reply With Quote
  #25  
Old 04-05-2010, 10:24 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

Hey Jim,

The code to do this is pretty simple. For example, using VBA, you could use:

Code:
Option Explicit

Private cmdBar As Office.CommandBar
Private button As Office.CommandBarButton

Sub CreateCommandBarButton()
    Set cmdBar = Application.CommandBars.Add(Temporary:=True)
    Set button = cmdBar.Controls.Add()

    Const smileyFaceId = 59
    button.FaceId = smileyFaceId

    button.OnAction = "MyMacro"
End Sub

Sub MyMacro()
    MsgBox "MyMacro called!"
End Sub
If you put the above in a standard VBA module and then run the CreateCommandBarButton() method, a CommandBar with a single smiley face button on it will be shown. Click on the button an the MyMacro sub will be called, displaying a msgbox.

The code to do this in VB.NET is almost identical, you'd only have to eliminate the Set keywords. (And possibly add some CType() commands for casting if using Option Strict on.

Of you wish to both create the CommandBar button in VB.NET and handle the click event directly within VB.NET, instead of using VBA to trap the click, then that could be done as well. This would require using direct event handling instead of using the OnAction property. It's somewhat more complex, but very doable if you need to.

Let us know if what's laid out here is good enough for you, or if you want to pursue other options...

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; 04-06-2010 at 12:10 PM.
Reply With Quote
  #26  
Old 04-06-2010, 12:06 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default xlApp question

Mike:

Thanks for the previous post. If this code is on the Server.xla, how do I define the xlApp object.

In other words, this will be "added" to an existing excel app. How do I declare xlAPP?


Jim
Reply With Quote
  #27  
Old 04-06-2010, 12:10 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

Oh, right, sorry my bad...

In VBA, the in-process 'Excel.Application' instance is accessed via the 'Application' global. I've now fixed the code above. Sorry about that!

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #28  
Old 04-07-2010, 10:54 AM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default I am still stuck

Mike:

I have the button showing and executing properly. However, What I need is for the Form of the vb.net app to know the current Row and Collumn of the Excel sheet. How can my vb.net execute a function that would go to the Active Sheet and get the row and column?

Also, I am also stuck on the GetFocus(Handle) which would toggle back and forth between the VB.Net and the Excel Sheet.


Jim
Reply With Quote
  #29  
Old 04-07-2010, 08:38 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

Hey Jim,

Quote:
Originally Posted by Jimc1611 View Post
I have the button showing and executing properly.
Cool.

Quote:
However, What I need is for the Form of the vb.net app to know the current Row and Collumn of the Excel sheet. How can my vb.net execute a function that would go to the Active Sheet and get the row and column?
I can think of two approaches:

(1) Have your button within Excel call back to your VB.NET application with the information for the row and column using the interface-based callback approach we discussed earlier.

(2) Have your VB.NET application use the Excel.Application instance to get the row and column via standard out of process automation.

I think you had version #1 working already before, yes?

For version #2, if your 'Excel.Application' instance were named 'excelApp', and if you wanted to get the row and column of the currently selected cell, then your VB.NET code could look something like this:

Code:
Dim rowNum As Integer = excelApp.ActiveCell.Row()
Dim columnNum As Integer = excelApp.ActiveCell.Column()
Quote:
Also, I am also stuck on the GetFocus(Handle) which would toggle back and forth between the VB.Net and the Excel Sheet.
This is getting a little beyond my expertise, but did you try the SetFocus API, as I suggested? If so, show your code, and describe what's happening or not happening.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #30  
Old 04-08-2010, 04:40 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default Here is my Code

Code:
Module Globals
    ' *** Change this filePath as required ***
    Friend Const filePath As String = "G:\Downloads\ExtremeVBTalk\Bidirectional VBA-.NET Communication\Bidirectional VBA-.NET Communication\"
    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 MyRow As String, ByVal MyColumn As String)
    Function GetData(ByVal MyRow As String, ByVal MyColumn As String)
    ReadOnly Property myhandle() As System.IntPtr

End Interface

<ComVisible(True)> _
<Guid("440F67D0-321A-42fb-BACD-D01C21C89443")> _
<ProgId("StandAloneExe.Form1")> _
<ClassInterface(ClassInterfaceType.None)> _
<ComDefaultInterface(GetType(IClient))> _
Public Class Client
    Implements IClient
    Delegate Sub InvokeDelegate(ByVal MyRow As String, ByVal MyColumn As String)
    ' Implement the 'IClient.CallbackToClient()' method.
    Public Sub CallbackToClient(ByVal MyRow As String, ByVal MyColumn As String) _
        Implements IClient.CallbackToClient
        ' TextBox1.Text = _excelApp.ActiveCell.Row()
        ' TextBox2.Text = _excelApp.ActiveCell.Column()
        'UpdateTextboxes(MyRow, MyColumn)
        ' StandAloneExe.Form1.TextBox1.BeginInvoke(New InvokeDelegate(AddressOf UpdateTextboxes))
        'MessageBox.Show("Row = " & MyRow & ", MyColumn = " & MyColumn)
    End Sub
    Public Function GetData(ByVal MyRow As String, ByVal MyColumn As String) _
        Implements IClient.GetData
        ' StandAloneExe.Form1.TextBox2.Text = MyColumn
        'MyRow = StandAloneExe.Form1.oWB.ActiveSheet.
        'MyColumn = StandAloneExe.Form1.oWS.
    End Function
    Public ReadOnly Property MyHandle() As System.IntPtr Implements IClient.myhandle
        Get
            MyHandle = StandAloneExe.Form1.Handle
        End Get
    End Property
End Class

Last edited by Colin Legg; 04-09-2010 at 06:41 AM. Reason: added code tags
Reply With Quote
  #31  
Old 04-09-2010, 05:47 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, you've presented code, but not explained any of the behavior: what's working, what's not. That leaves me to guess...

From what I can see:

You've added a 'ReadOnly Property myhandle() As System.IntPtr' within the COM-visible 'IClient' interface in order to pass the window handle from the VB.NET EXE to the add-in running within Excel. This is fine, except that I don't think that 'System.IntPtr' is COM-visible, so it won't work as part of a COM-visible interface.

Instead, I would change this parameter to 'Integer', which VBA will receive as 'Long'.

Another idea is to control focus from the VB.NET side of the equation, instead of from the VBA side. This is much easier because VB.NET knows the window handle for it's own form and it can get the window handle for the Excel application via the 'Excel.Application.Hwnd' property.

This is about all I can see. If you explain more about the behavior that you are getting or what you are trying to achieve, I might be able to suggest more.

Also, I'm going to be away this coming week, out of town on a consulting assignment. I will not have the ability to check into this forum during that time, so this will be my last day on the forum for a while...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #32  
Old 04-09-2010, 02:13 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default What is the oppisite of workbook.activate?

Mike:

I am attempting to do the following in the vbServere.xla:

Sub MyMacro()
If Not (m_Client Is Nothing) Then
Windows("Command Workbook.xls").Activate
'ActiveWorkbook.ActiveSheet.
Call m_Client.GetData
End If

End Sub

I actually want to minimize or move the excel app down to the taskbar.

What is the oppisite of Activate?

Jim
Reply With Quote
  #33  
Old 04-09-2010, 03:28 PM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default I got it

Mike:

FYI. in my vb.net code I did

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
oWB.Activate()
oWS.Activate()

Finally
End Try
End Sub

then in my VBA

Sub MyMacro()
If Not (m_Client Is Nothing) Then
Application.WindowState = xlMinimized

Call m_Client.GetData
End If

End Sub

FYI in my .net

Public Sub GetData() Implements IClient.GetData
StandAloneExe.Form1.Activate()
End Sub

Private Sub Form1_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
If Me._excelApp IsNot Nothing Then
TextBox1.Text = _excelApp.ActiveCell.Row()
TextBox2.Text = _excelApp.ActiveCell.Column()
End If
End Sub

That allows me to toggle and for the form to get the Cell/Row number.

Thanks for your trmendous help.

I am more familiar with vb.net. Please recommend some VBA reference books to learn from -- especially automation.

Jim
Reply With Quote
  #34  
Old 04-09-2010, 04:20 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

Hey Jim,

Yes the "opposite of Activate", is "minimize", glad you got it.

Overall, though, it sounds like you are going through an awful amount of work to have Excel control your VB.NET exe. If your VB.NET exe is in control of Excel, then this is fine... But your control is going the other way. It can work, but it feels like you are making things very hard on yourself.

Have you considered having your program simply reside as an add-in within Excel, instead of having Excel control your VB.NET exe?

As for an Excel VBA book, my top recommendation, by far, is Excel 2007 VBA Programmer's Reference by Bullen, Green, et al. The code for calling Excel via VB.NET is almost identical to VBA, so it's an excellent resource for both.

Hope this helps!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #35  
Old 04-23-2010, 09:24 AM
Jimc1611 Jimc1611 is offline
Newcomer
 
Join Date: Mar 2010
Posts: 18
Default

Mike:

You stated:

"Have you considered having your program simply reside as an add-in within Excel?"

How do I do this? ie via a vba button click.

I have ordered the book you referenced.


Thanks


Jim
Reply With Quote
  #36  
Old 04-24-2010, 09:43 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,

The standard way to do this using .NET would be to use a managed COM add-in. You had this running back in Post#5 within this thread. Another way using .NET is to use VSTO, this is a more complex solution though, with more difficult deployment. I think that a managed COM add-in would definitely be good enough for you.

Another way to go is to create a VBA add-in (.xla file for 2003 or .xlam file for 2007). This is well covered in that VBA book that you have ordered. But I think that the VBA book is best for learning the Excel object model -- I would stick with .NET for your own coding at this point.

The idea of using an Excel add-in, instead of using a stand-alone executable to bidirectionally communicate with Excel, is that if you had all your code executing from within an Excel add-in, then you would not have to worry about out-of-process communication between your two solutions, because there would only be one program running: the add-in running directly within Excel.

But if you already have your VB.NET executable running and it is only the communication with Excel that needs to be perfected, then it might make the best sense to keep your existing solution. I can't answer this for you because I can't say where you are in your development.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #37  
Old 07-16-2011, 03:32 AM
loveforum8808 loveforum8808 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 1
Default Thanks for the thread and your posting

Today I come here to study and share some my owns.Now I see your posting.What I want to say is that I laugh instead of the phrase.Because it is very important for me.Hope you can come to my area to appreciate my posting.We can study somethion for each other.Thanks for the thread and your posting.In return for my reply,you must go to my ares to go and see.By the way,do you know Coach outlet Handbags or Coach Outlet Online Sale 4 You?You can see them!
Points to leave
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
 
-->