Setting Reference Library within Macro

Afternoon,

I have a workbook with two macros that don't get along. If the reference needed for the first is active, then the second crashes. So, I'm trying to manually set the reference within the first macro, then deactivate it at the end using a GUID.

Dim refMSO12 As References
Set refMSO12 = New References

'Adds in the Microsoft Office 12.0 Object Library
strGUID = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
ThisWorkbook.VBProject.References.AddFromGuid(strGUID, 2, 4) = refMSO12

.... Macro Content ....

With References
.Remove refMSO12
End With

It crashes on the Set refMSO12 = New References with a Run-Time Error 429: ActiveX component cannot create object. If I take that line out, it does actually add in the library, but also gives another errror of 438: Object doesn't support this property or method (doesn't highlight whats wrong either).

Thanks much for your help!

Carl

Okay changed it, and it looks like its sort of setting it up. I'm using a msoFileDialogFolderPicker, reliant on 12.0 object library, which fails. But if you check after the reference is selected. So, I'm a tad confused.

Isn't this late binding since I'm not checking each of the references off in the Tools --> References box?

When you type msoFileDialogFolderPicker into the VB editor it pops up with a message saying it needs to add another reference, if it isn't added a method failed error message comes up when the program is run.

Its generating an error within the second macro whenever that reference is selected; Compile Error: Assignment to constant not permitted, and highlights msoShapeRectangle = 1. If you keep them separate its fine, but it'd be much nicer if they were all contained within a single workbook.

I don't want to mess around with the code of second macro as it's running statistics on samples and I can't impact the way it handles all that. So, I was trying to make the other macro compatible.

Haha I have no idea, I didn't make it. But it works as long as I don't fiddle with it.

Doesn't seem to be working without that line. So, I guess I'll stick with trying to turn the reference on and off.

Is their a lag period between turning it on and using it? The program gives an error like it hasn't turned on (mso folder picker gets mad) but when you check the references box the office 12 object library is selected.

Dim refMSO12 As References
Set refMSO12 = New References

'Adds in the Microsoft Office 12.0 Object Library
strGUID = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
ThisWorkbook.VBProject.References.AddFromGuid(strGUID, 2, 4) = refMSO12


' Get the import directory from the user
With Application.FileDialog(msoFileDialogFolderPicker) ' --> Acts like the reference isn't there
.Show
If .SelectedItems.Count = 0 Then Exit Sub 'user cancelled
strFolder = .SelectedItems(1)
End With

Thanks!

Hey I actually got it working. It's better now that the addition and removal of the references are done in separate subs.

'Adds in the Microsoft Office 12.0 Object Library
AddReference

' Get the import directory from the user
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
    If .SelectedItems.Count = 0 Then Exit Sub 'user cancelled
        strFolder = .SelectedItems(1)
    End With

RemoveReference


........ macro code.........


Private Sub AddReference()

Dim Reference As Object

With ThisWorkbook.VBProject
For Each Reference In .References
If Reference.Description Like "Microsoft Office 12.0 Object Library" Then Exit Sub
Next
.References.Addfromguid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", 2, 4
End With
End Sub



Private Sub RemoveReference()

Dim Reference As Object

With ThisWorkbook.VBProject
For Each Reference In .References

    If Reference.Description Like "Microsoft Office 12.0 Object Library" Then
        ThisWorkbook.VBProject.References.Remove Reference
    End If

Next

End With
End Sub

Thanks for all the help!

Carl