Excel Multiple Instances - (1) VB.NET API Excel Interface app (2) Excel VBA coded program app

[TexasFM3]

Hello All,

My First Post but trust a freaking long time user.

OK so this is a new one for me and I always seem to get the fun stuff.

I have a simple Excel VBA Workbook program that has worked fine up till now just using its internal calculations. However, recently a rigorous program that calculates the same values but with more accuracy as I do in my VBA workbook has added a API to its install. Problem is it is in VB.NET. VBA and VB.NET do not play well with one another and the VBA Interopt would require VisualStudio.NET as well as learning VB.NET. So my code is still out of luck to communicate to the program. OR IS IT, Bwahahahaha!

As part of the Rigorous Program install, they deliver a API Excel Tool Workbook. Of course there is no code seen, due to it being in VB.NET and written using Visual Studio.NET. BUT I have a working Excel VB.NET workbook that interfaces the rigorous app and if I can populate my VBA workbook information into that Excel file, run the VB.NET workbook app, and read back the results into my VBA program then life would be golden.

The first problem was opening the demo. When it opens, it auto connects to the rigorous app. When it does this it prompts the user "that it is connecting to the application" then needs a user input to push the OK button. Thus, already a problem because the code locks up till the user pushes OK. So my first solution - Application.DisplayAlerts = False. This VBA code, however, does not work for the VB.NET creation, and I assume it is because VBA and VB.NET do not mix and the code simply gets ignored

So to get past this I run the .NET workbook from a VBA programmed shell command and open the VB.NET workbook in a second instance of Excel, then use WINDOWS API to click the OK button. This worked like a champ.

Problem now is I am trying to send values from my Excel VBA program in one instance of Excel to another instance of Excel that is running the VB.NET workbook.

So how can I connect to the opened second instance of Excel and place my values into it. Heck, I wish I did not need the second instance of Excel and get the two combined into a single instance again, however, there does not seem to be a way to do this.

I am open to any suggestions.

Thanks

 

[RoryA]

Why do you need to use a second excel instance to use API calls? Even if you do, you should be able to automate it rather than using Shell, so that you have a reference to the relevant COM objects.

 

[TexasFM3]

The second instance is simply so my code proceeds onward. If I open the Excel VB.NET workbook, that workbook is written such that it auto connects to the Rigorous Application and prompts with a OK Button that the user must manually acknowledge.

Since DisplayAlerts does not work, I had to open this in a second instance of Excel so my VBA code would continue to run. The only reason I chose Shell was since I was now using Win API to push all the buttons that was created in VB.NET. VBA cannot be used with VB.NET so have to use Win API to do these functions.

I do like your idea about simply opening up the Excel .NET app via VBA in a second instance of Excel so I can have a ref. I tell you man - looking at this, I lost sight of the forest due to all the freaking trees, hehehe.

I will try this and update!

Thanks for helping me see the forest!

 

[TexasFM3]

Awe man - This is killing me - So I tried to start another instance of excel - and due to the initial auto connect of the workbook, the code locks up tighter than a water rats backside until the window is acknowledged.

Sub OpenT2()
'131015 FM3 rev6.3a Because the T2APITool locks excel access due to prompts _
all of Excel remains locked and the code will not progress until _
buttons are pushed. Need to Launch T2APITool in its own Instance

Dim xlT2App As Excel.Application
Dim strPath As String
Dim sWorkbookToOpen As String
Dim sWorkbook As String

strPath = "C:\Program Files\T2\Bin\"
sWorkbook = "T2Excel.xlsx"
sWorkbookToOpen = strPath & sWorkbook

'Create new Excel object
Set xlT2App = CreateObject("Excel.Application")
xlT2App.Application.Visible = True
xlT2App.Visible = True

'Open current file in the newly created version of Excel
xlT2App.Workbooks.Open sWorkbookToOpen

'''' 'Commented out to test out the opening of 2nd instance of Excel and hope code proceeds onward
'''' SHELL "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE """ & sWorkbookToOpen & """"

End Sub

Looks like I am back to using the Shell command version to open the 2nd instance of Excel. This of course leads me back to the original problem of finding this workbook and populating it with my data from my current VBA workbook.

 

[RoryA]

In that case, perhaps you can stick with your original shell version, then use:

Dim wbk as workbook
set wbk = getobject("full path to workbook here")

after you have run your APIs to dismiss the message.

You might also find it useful to discuss this with whomever was responsible for creating the workbook/app. It is entirely possible to write libraries in .Net that are COM enabled.

 

[TexasFM3]

Rory... I tell you great minds think alike - I just did program in the getobject and then see your email and viola!

On the case of the VB.NET API Tool rewrite, you know its always about the all mighty greenback... sure they COULD do it, but someone needs to get paid, thus I am stuck trying to get a square peg into a round hole!

I'll post up the solution for some other poor soul once I cross this beast off my list!

Thanks for the insight and getting my mind pumping on all cylinders this morn.

PS: aside from great minds thinking alike so do psychotic minds - I am not quite sure which I am for taking this bull by the horns

 

[TexasFM3]

No Problem on the CODE tags. But first i need a solutions... hahahha

Ok... I have worked myself into a corner and again these User prompts are killing me. So I have two excel instances as explained above.

Well the first prompt opens up automatically when the Excel interface opens. This is why I had to Spawn the 2nd instance of Excel via a shell command.

'Commented out to test out the opening of 2nd instance of Excel and hope code proceeds onward
SHELL "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE """ & sWorkbookToOpen & """"

Well now there is a push button on the 2nd instance of Excel that I must push. I do this via sendmessage - but dog gone it... the code locks up again. This even though the sendmessage is going to the 2nd instance of Excel and done via WIN API!!!!

The code follows.

Public Sub T2DesignButton()
'when working with Win API everything is dependent on _
    the handle of the window.  If we can get the handle _
    of the window we are working and the path to the _
    place we need to get to then we are golden!
Dim hT2APITool_window As Long
Dim hXLDesk_window As Long
Dim hWrkBook_window As Long


'Lets initialize the handle to zero
hT2APITool_window = 0

'I wanted to set the T2APITool to not display any alerts because when we push the _
    Design Button the T2 interface prompts with a "succeed" or a _
    "fail" prompt that requires User to acknowledge.  We can tell if the _
    design is successful or failed by any values are generated or not.  However, because _
    .NET was used for the API, i am unable to get the DisplayAlerts command to work. 

'xlT2App.DisplayAlerts = False <-commented out cause no worky :(

    'Lets find the handle of the T2 Interface Tool so we can then drill _
        down the handles and find and push the Design Button.
        
    'MAIN EXCEL window
    hT2APITool_window = FindWindow(vbNullString, "Microsoft Excel - T2API.xlsx")

    'we now find the handle for the XLDESK class.  In Excel, the sheet is actually opened in this _
        child window of the main excel window.
    hXLDesk_window = FindWindowEx(hT2APITool_window, 0&, "XLDESK", vbNullString)
 
    'We now find the handle for the workbook that is opened in the XLDesk handle.
    hWrkBook_window = FindWindowEx(hXLDesk_window, 0&, vbNullString, "T2API.xlsx")

    'becasue the Design button that we need to push is under a generic Windows Form class _
        with many possible type of objects we need to start at the hWrkBook handle and _
        find the two handles (1 for the Generic Class and 1 for the button object) as a _
        child/parent pair.  I recursively obtain the window handles given a "parent" _
        handle to search for the button that I know exists in the GenericClass
    FPCount = 0
    FindPath hWrkBook_window

'Lets make the design button window the active window on the screen so that the click will be _
    assurred of getting accepted

'Let activate the correct T2APITool worksheet for us to work with
'xlT2App.Worksheets("ST").Activate <-This was commented out to check if the object or the sendmessage was the cause of the code lock

'Lets make the DesignButton window the active window on the screen so that the click will be _
    assurred of getting accepted
SetActiveWindow hDesignButton_window

'We click the freakin button and code locks up waiting for user to click the prompt
Call SendMessage(hDesignButton_window, BM_CLICK, 0, ByVal 0&) 

'DisplayAlert proved to not work with .NET code.<- :( man this would save so much freakin time if the DisplayAlerts FREAKING WORKED
'xlT2App.DisplayAlerts = True

Sleep 100

'Need to use Windows API to push the OK button on the prompt when it opens - Of course we never get here cause the prompt opens and locks the code
Call T2Prompt


End Sub

So I am at a end anyone know how to over come this problem?

1) either a way to sendmessage the second instance from the 1st instance without the code locking and the code continues to run

2) get the freaking DisplayAlerts of the 2nd instance of Excel to work. It has to be because the T2API is programed with VB.NET is all I can think of as a reason for it not.

Thanks...

 

[RoryA]

Have you checked you actually have the correct window handles at each stage? A workbook window should be class EXCEL7, by the way.

 

[TexasFM3]

Hello Rory,

Yes, I have checked . Trust everything triggers as expected. I just was not expecting the Code hangup when the sendmessage to the 2nd instance of Excel was BM_CLICKED

Yes Excel7 is the class, but with multiple instances of Excel and multiple worksheets I went with the name of the sheet of the API interface (which wont change) rather than creating a loop.

So the line below could have been used for class search or name search, but I went with the name search to get handle for the workbook.

hWrkBook_window = FindWindowEx(hXLDesk_window, 0&, vbNullString, "T2API.xlsx")

As for my issue with the code hanging, I beat back that beast as follows:

I replaced the code

'We click the freakin button and code locks up waiting for user to click the prompt 
Call SendMessage(hDesignButton_window, BM_CLICK, 0, ByVal 0&)

with the following

'We click the freakin button
Dim resApi As Long
Call SendMessageTimeout(hDesignButton_window, BM_CLICK, 0, ByVal 0&, SMTO_NORMAL, 100, resApi)

Yes I know this is not really the correct way, but dang it, I am coming to the end of sanity! The thing freakin works so YAY ME!

Now on to the next problem

 

[RoryA]

My suggestion was meaning to use:

hWrkBook_window = FindWindowEx(hXLDesk_window, 0&, "EXCEL7", "T2API.xlsx")

not

hWrkBook_window = FindWindowEx(hXLDesk_window, 0&, "EXCEL7", vbNullString)

but hey, glad you got it sorted.