loading object libraries / references dynamically

Ingo said:

How can we read the loaded references and load appropriate versions dynamically?

That's a question for Microsoft, who invented COM without much forethought about your kind of problem.

One approach is to eschew the COM libraries and use object creation by name...

Dim oExcel As Object
Set oExcel = CreateObject ("Excel")

CreateObject looks for whatever COM class is registered with name Excel.  That should work on different computers with different versions of Excel.

What you lose by that approach is interface binding — you no longer see hints about object properties and methods, and VBA can't check at compile time that you're calling the interface correctly.   Build your prototype using the Excel library reference, and for production create a version using CreateObject.

Ingo said:

How can we read the loaded references and load appropriate versions dynamically?

That's a question for Microsoft, who invented COM without much forethought about your kind of problem.

One approach is to eschew the COM libraries and use object creation by name...

Dim oExcel As Object
Set oExcel = CreateObject ("Excel")

CreateObject looks for whatever COM class is registered with name Excel.  That should work on different computers with different versions of Excel.

What you lose by that approach is interface binding — you no longer see hints about object properties and methods, and VBA can't check at compile time that you're calling the interface correctly.   Build your prototype using the Excel library reference, and for production create a version using CreateObject.

Hi guys,

thanks for he replies,

@Jan
sorry I'm no coder - so I don't konw exactly what information you looking for.
Maybe you can please help me what you need exactly.

@Jon
bit the same - I don't know where to start with your information.

I was thinking of somthing like this what I found for Excel vba.
Is this adaptable for Microstation?

Public Sub prcShowReference()
    Dim objReferences As Object
    For Each objReferences In Workbooks("Test.xls").VBProject.References
        Debug.Print objReferences.FullPath, objReferences.Name, _
            objReferences.GUID, objReferences.Major, objReferences.Minor
    Next
End Sub

Ingo said:

Sorry I'm no coder — I don't know where to start with your information

You can't solve your problem without someone experienced with VBA.  When in-house experience is not available, then hire a contractor. Jan is not too far away from Frankfurt. 

okay - I've been coding in vba for some years but I was not taught in this field.
So with some push in the right direction I think I can handle the task.

But I'm not familiar with COM classes. So any further advice for that could help.

Hi Ingo,

I added two common Microsoft VBA code snippets on our VBEIDE page that we occasionally have to recommend to users in need of troubleshooting their Microsoft VBA projects.

Feel free to try the VBEProjectReferences code snip to see if it helps provide the information needed.

If or when you encounter any issues, please see our MVBATroubleshooting page for some common troubleshooting steps that may be required.

Please note this is a MicroStation (not Microsoft) programming forum available to help users productively Search and Ask programming questions.

Since your question is purely a Microsoft issue (not MicroStation specific) you may find more helpful Microsoft responses on Microsoft Forums; however this forum certainly helps users that have a desire to learn and create their own Microsoft and MicroStation custom code solutions in the process.

HTH,
Bob

Ingo said:

I'm not familiar with COM classes

VBA is COM.  Each class you create in VBA is a COM class and each UserForm is a COM class.  So you may not think you are familiar with COM, but you're using it all the time when you write VBA code.

The key part of COM is that it defines an interface.  Other apps., whether VBA, C++ or .NET, can use that interface.  VBA can use an interface defined by some other app.

This COM Server article shows how that is done.

Thanks Robert,

that looks very promising and is the direction I was looking for.

I'll dig into that.

cheers,
Ingo

for anyone who has similar questions: this is my result at the moment for switching to the correct ecxel vba support:

two routines: one detaches, one attaches.

thanks for all advice so far.

'needs VBA reference
' Add: Tools > References: "Microsoft Visual Basic for Application Extensibility 5.3"
'
    
Private Const stExcelGUID As String = "{00020813-0000-0000-C000-000000000046}"
Option Explicit


Public Sub VBAdetachXls()
    Dim oVBE                As VBE
    Dim oProject            As VBIDE.VBProject
    Dim oReferences         As VBIDE.References
    Dim oRef                As VBIDE.Reference
    
    Dim stMessage           As String
    Dim lgMinor            As Long
    
    Dim i                   As Integer
    
    Set oVBE = Application.VBE
            
    Set oReferences = oVBE.ActiveVBProject.References
    For Each oRef In oReferences
       
        If oRef.GUID = stExcelGUID Then
        
            lgMinor = oRef.Minor
            If lgMinor = 9 Then
                oVBE.ActiveVBProject.References.Remove oRef
                Debug.Print "detached vers. 9"
                i = i + 1
            End If
            
            If lgMinor = 8 Then
                oVBE.ActiveVBProject.References.Remove oRef
                Debug.Print "detached vers. 8"
                i = i + 1
            End If
            
        End If
       
    Next
    
    If i = 0 Then
        stMessage = "nothing detached"
        Debug.Print stMessage
    End If
    
    'MsgBox stMessage
    Debug.Print "###"
    
End Sub

'
'try to attach latest xls
'error if is attached
'

Public Sub VBAattachXls9()

    Dim stMessage           As String

    On Error GoTo IsAttached
    Application.VBE.ActiveVBProject.References.AddFromGuid stExcelGUID, 1, 9
    On Error GoTo 0
    
    stMessage = "vba excel vers.9 attached"
    Debug.Print stMessage
    Debug.Print "###"
    'MsgBox stMessage
    Exit Sub
    
IsAttached:
    stMessage = "ref is already attached or not found"
    Debug.Print stMessage
    Debug.Print "###"
    'MsgBox stMessage
    Exit Sub
    
End Sub

Ingenious!