CODEWITHSUNDEEP
vb.netexcelvba
Moosli
Moosli

Reputation: 3275

GetActiveObject from The Excel Workbook

I have a Datepicker written in vb.net 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 vb.net 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

    Try
        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            
        Me.Close()
    End Try
End Sub

If you now click on a Date the vb.net 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

    Try
        '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()
                Me.Close()
            End If

        Next
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Me.Close()
    End Try

End Sub

Upvotes: 2

Views: 1221

Answers (2)

Slai
Slai

Reputation: 22876

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 ": https://msdn.microsoft.com/en-us/library/system.environment.getcommandlineargs

Upvotes: 1

RCaetano
RCaetano

Reputation: 673

Note: This is not the solution but I am posting it here to avoid long code in the comments (to improve readability). I will leave this answer here since the OP comment to this answer provides more information about his problem.

Maybe you should change:

arg = ActiveWorkbook.Path & ";" & ActiveWorkbook.Name & ";" & ActiveSheet.Name & ";" & Target.Address

to

arg = ThisWorkbook.Path & ";" & ThisWorkbook.Name & ";" & ActiveSheet.Name & ";" & Target.Address

At least you are referencing the workbook that's currently running the code and since you may have several workbooks open that could change the ActiveWorkbook as you are navigating between them.

HTH ;)

Upvotes: 1

Related Questions