Can a Reference be turned on and off by code (1 Viewer)

[smiler44]

As per title, can a reference be turned on and off by code?

I have a problem with the Reference Microsoft Scripting Run Time.
I have no idea why but it causes a problem with some other code.

Can I use code to turn this reference on and off? If so how please?
thanks
smiler44

 

[smiler44]

sorry should have said excel 2010 but would like to do it also in 2007.
i have found code on the internet but cant make it work for me so i guess it is possible but if you can give me the code to do it please
thanks
smiler44

 

[CJ_London]

sounds like you need to research using late binding which will take account fo different versions.

This link explains the difference

http://support.microsoft.com/kb/245115

 

[smiler44]

Thanks CJ_London. that is one heavy artical. Looks like early binding will work if i write in 2010 and run in 2007.
Still need to know the code for doing it if you could please
smiler44

 

[CJ_London]

Looks like early binding will work if i write in 2010 and run in 2007

for early binding, there is no code, just use the references. But I suspect you haven't understood the link - why would you think that something written in later environment will work in an earlier one?

Strongly recommend you need to research using late binding - depending on what you want to do the code has a similar structure but will vary depending on what you are late binding - the code is in the link under late binding - OK it is for an Excel application, but the principle remains the same.

Here is another link

http://superuser.com/questions/615463/how-to-avoid-references-in-vba-early-binding-vs-late-binding

just google 'late binding file scripting' you'll find plenty of links

i have found code on the internet but cant make it work for me so i guess it is possible but if you can give me the code to do it please

it would be easier for me to tell you how to build a spaceship, your question is so vague. I have absolutely no idea what you want to do so how can I 'give you the code'

 

[smiler44]

sorry cj_London I'll re read the link again and look at the new one you have given me.
I need to use code to turn on and off the reference Mircosoft Scripting Runtime. I need it on to run some of my code but then turn it off as it is causing a problem with another part of my code. No idea why and so I thought a work around would be to try and turn it on and off as required.

I thought from the link early binding would allow my code to work with an earlier version of Excel


smiler44

 

[CJ_London]

I thought from the link early binding would allow my code to work with an earlier version of Excel

No - it means it works with a specific version, or at least a version that uses the same reference

late binding will work with any version

 

[smiler44]

CJ_London I have had a read of http://superuser.com/questions/61546...s-late-binding and got confused


part of the artical says

Attention: If you use Late Binding, underlying numerical values of named variables are unknown to the system. You have to convert those named variables to their corresponding numerical value.
To get the numerical value, temporarily re-enable your library (e.g. Microsoft Scripting Runtime), go to the VBA editor and open the immediate window (CTRL+G). Write for example ?TemporaryFolder into the immediate window and press enter. As you see in the screenshot 2 is the value of our named variable TemporaryFolder


what is 2 the numerical value of? Temporaryfolder or microsoft scripting run time? I'll take a guess its microsoft scripting run time as with it ticked i got 2 and witout it i got nothing.


Using the above link and information this would I think overcome compatibility issues between different versions of excel. I still have the problem of.... how do I turn the reference Microsoft Sripting runtime on and off by code?

smiler44

 

[smiler44]

not one to give up i have found all these on the internet. may also be help to others.
not tried any of this in my project but in a new workbook and i know the first 4 work, i have not tried the last one.

the first and last seem to be the ones I am looking for

Sub CreateRef_Library_ScFso()
' turns on microsoft scripting runtime reference
'// Set a Reference to SCRRUN.DLL = Scripting.FileSystemObject
'// Microsoft scripting Runtime
'// If it already exits Resume & Handle latter!
Dim ID As Object
On Error Resume Next
Set ID = ThisWorkbook.VBProject.References
ID.AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
  
If Err.Number <> 0 Then
    MsgBox ("Err.Number & Err.Description & Err.HelpFile & Err.HelpContext & Microsoft scripting Runtime")
End If
End Sub
 
 
Sub ListReferences()
' gives a messeage box showing each reference
Dim oRef As Object
 
For Each oRef In ActiveWorkbook.VBProject.References
    MsgBox oRef.Description
Next oRef
End Sub
 
 
Sub ListReferences2()
'list the references and their GUID reference

  Dim oRef As Object
  For Each oRef In ActiveWorkbook.VBProject.References
    'MsgBox oRef.Description
    'MsgBox oRef.guid
    Debug.Print oRef.Description
    Debug.Print oRef.GUID
    Debug.Print
  Next oRef
End Sub

Sub ListReferences3()
'enable the reference to "Microsoft Visual Basic For Applications Extensibility".
' fills in work sheet with reference name, path, GUID reference

Dim ref As reference
Dim lrow As Long
 
Range("A1:F1") = Array("Name", "Description", "FullPath", "GUID", "Major", "Minor")
lrow = 2
For Each ref In ThisWorkbook.VBProject.References
    Range("A" & lrow) = ref.Name
    Range("B" & lrow) = ref.Description
    Range("C" & lrow) = ref.FullPath
    Range("D" & lrow) = ref.GUID
    Range("E" & lrow) = ref.Major
    Range("F" & lrow) = ref.Minor
    lrow = lrow + 1
Next ref
End Sub
 
smiler44

Sub RemoveReference()
' this should turn off microsoft scripting runtime
Dim sRefPathName As String, i As Integer
 
sRefPathName = "c:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
 
With ActiveWorkbook.VBProject
    For i = 1 To .References.Count
        If LCase(.References(i).FullPath) = LCase(sRefPathName) Then
            .References.Remove .References.Item(i)
            Exit For
        End If
    Next i
End With
End Sub

 

[CJ_London]

what is 2 the numerical value of? Temporaryfolder

Temporaryfolder.

You can also find all the values using the object browser. In the vba window either select view>object browser or hit F2, then type into the searchbox the value you are looking for - in this case temporaryfolder.

I suggest you investigate why using a reference causes a problem with your code - have you tried compiling for example? mPerhaps you have declared a variable names the same as something in the reference for example.

I suspect the code in your later post will only work in an mdb or accdb and in a full environment, i.e. won't work in runtime.