Moosli Moosli - 1 year ago 68 Question

GetActiveObject from The Excel Worbook

I have a Datepicker written in that looks like this.

enter image description here

I had to do it this way because of some Security Settings we have. Because of that, the Build in Datepicker from Excel generate sometimes an Error.

The Date Picker works fine if there Is Only One Excel Application Open. The Problem is when there are Multiple Excel Applications Open.

enter image description here

The code only takes the first Excel Application, but I want to get the Excel Workbook that's called "Test".

I Think the problem is this statement:

objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

For a better understanding the Code from Excel and the DatePicker Application is below:

That's how I open the Application in Excel:

Set wsh = VBA.CreateObject("WScript.Shell")
arg = ActiveWorkbook.Path & ";" & ActiveWorkbook.Name & ";" & ActiveSheet.Name & ";" & Target.Address
' Wait for the shelled application to finish:
errorCode = wsh.Run(strPathDatePicker & " " & arg, windowStyle, waitOnReturn)

The code for the DatePciker is:

Public Sub frmDatePicker_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim Par() As String
Dim strTemp As String

Dim arg = Environment.GetCommandLineArgs
strTemp = arg(1)
Par = Split(strTemp, ";")

'Split arg, to get the Information from the Excel Workbook
strWbPath = Par(0)
strWbName = Par(1)
strWsName = Par(2)
strAdresse = Par(3)

Catch ex As Exception
End Try
End Sub

If you now click on a Date the Application will run this code for inserting the selected date into Excel:

Private Sub MonthCalendar1_DateSelected(sender As Object, e As DateRangeEventArgs) Handles MonthCalendar1.DateSelected
Dim objExcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

'Get the Excel Object
objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

For Each wb In objExcel.Workbooks
If wb.Name = strWbName Then
ws = wb.Sheets(strWsName)
ws.Range(strAdresse.ToString).Value = e.Start()
End If

Catch ex As Exception
End Try

End Sub

Answer Source

The usual ways would probably be to pass the process Id, or Application.Hwnd, or even the risky ActiveWindow.Caption, but I think just the full external address is enough to find the Excel instance. For example in VB.Net (not tested):

Dim o As Object = GetObject("Book 1.xls")
Dim wb As Excel.Workbook = TryCast(o, Excel.Workbook)

Note that ; is valid character in file, workbook, worksheet, and named range names so I would recommend looking for a different separator. For example non-printable characters like Chr(0) might work, or just use the full external address Target.Address(,,,1).

Environment.GetCommandLineArgs(1) will cause problems if the argument contains spaces and is not surrounded by ":