+ Reply to Thread
Results 1 to 16 of 16

Dynamic References to Word?

  1. #1
    Newbie
    Guest

    Dynamic References to Word?

    Hello
    As the Late Binding seems very slow, I would like to create a reference on
    Word 2000 or Word 2003 (Several PCs are running Office 2000, some others are
    running Office 2003.
    How can i create a Reference for Microsoft Word 9.0 or for Microsoft Word
    11.0 (2000 and 2003)?
    How to remove this Reference after the VBA procedure has done its work?
    Thanks



  2. #2
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    Sub ActivateWordLibrary()

    Dim R

    On Error Resume Next

    'no need to carry on if the Word Object Library is already there
    '---------------------------------------------------------------
    For Each R In ThisWorkbook.VBProject.References
    If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    Exit Sub
    End If
    Next

    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:="{00020905-0000-0000-C000-000000000046}", _
    Major:=0, Minor:=0

    On Error GoTo 0

    End Sub

    You can remove the reference after, but not really neccessary.

    RBS


    "Newbie" <newbie@libre.fr> wrote in message
    news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > Hello
    > As the Late Binding seems very slow, I would like to create a reference on
    > Word 2000 or Word 2003 (Several PCs are running Office 2000, some others
    > are
    > running Office 2003.
    > How can i create a Reference for Microsoft Word 9.0 or for Microsoft Word
    > 11.0 (2000 and 2003)?
    > How to remove this Reference after the VBA procedure has done its work?
    > Thanks
    >
    >



  3. #3
    Newbie
    Guest

    Re: Dynamic References to Word?

    Thanks a lot RB Smissaert !

    1° - Does this procedure work if Word 2000 is installed as well with Word
    2003 installed?
    2° - I need to remove the reference because another VBA procedure (which
    doesn't uses Word) hangs if the reference is still there!
    Thanks again !

    Newbie

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > Sub ActivateWordLibrary()
    >
    > Dim R
    >
    > On Error Resume Next
    >
    > 'no need to carry on if the Word Object Library is already there
    > '---------------------------------------------------------------
    > For Each R In ThisWorkbook.VBProject.References
    > If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > Exit Sub
    > End If
    > Next
    >
    > ThisWorkbook.VBProject.References.AddFromGuid _
    > GUID:="{00020905-0000-0000-C000-000000000046}", _
    > Major:=0, Minor:=0
    >
    > On Error GoTo 0
    >
    > End Sub
    >
    > You can remove the reference after, but not really neccessary.
    >
    > RBS
    >
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > > Hello
    > > As the Late Binding seems very slow, I would like to create a reference

    on
    > > Word 2000 or Word 2003 (Several PCs are running Office 2000, some others
    > > are
    > > running Office 2003.
    > > How can i create a Reference for Microsoft Word 9.0 or for Microsoft

    Word
    > > 11.0 (2000 and 2003)?
    > > How to remove this Reference after the VBA procedure has done its work?
    > > Thanks
    > >
    > >

    >




  4. #4
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    Yes, should work with all versions of Word as it does Major:=0, Minor:=0.


    To remove do this:

    Sub RemoveWordReference()

    RemoveReference "Word"

    End Sub

    Sub RemoveReference(strReference As String)

    On Error GoTo ERROROUT

    Dim R As Object

    For Each R In ThisWorkbook.VBProject.References
    If R.Name = strReference Then
    ThisWorkbook.VBProject.References.Remove R
    Exit Sub
    End If
    Next

    ERROROUT:
    On Error GoTo 0

    End Sub

    As it does Dim R As Object, rather than R as Reference you don't need a
    reference to
    Microsoft Visual Basic for Applications Extensibility.


    RBS


    "Newbie" <newbie@libre.fr> wrote in message
    news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > Thanks a lot RB Smissaert !
    >
    > 1° - Does this procedure work if Word 2000 is installed as well with Word
    > 2003 installed?
    > 2° - I need to remove the reference because another VBA procedure (which
    > doesn't uses Word) hangs if the reference is still there!
    > Thanks again !
    >
    > Newbie
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> Sub ActivateWordLibrary()
    >>
    >> Dim R
    >>
    >> On Error Resume Next
    >>
    >> 'no need to carry on if the Word Object Library is already there
    >> '---------------------------------------------------------------
    >> For Each R In ThisWorkbook.VBProject.References
    >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    >> Exit Sub
    >> End If
    >> Next
    >>
    >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> Major:=0, Minor:=0
    >>
    >> On Error GoTo 0
    >>
    >> End Sub
    >>
    >> You can remove the reference after, but not really neccessary.
    >>
    >> RBS
    >>
    >>
    >> "Newbie" <newbie@libre.fr> wrote in message
    >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> > Hello
    >> > As the Late Binding seems very slow, I would like to create a reference

    > on
    >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    >> > others
    >> > are
    >> > running Office 2003.
    >> > How can i create a Reference for Microsoft Word 9.0 or for Microsoft

    > Word
    >> > 11.0 (2000 and 2003)?
    >> > How to remove this Reference after the VBA procedure has done its work?
    >> > Thanks
    >> >
    >> >

    >>

    >
    >



  5. #5
    Newbie
    Guest

    Re: Dynamic References to Word?

    Thanks a lot RBS !
    Very useful!
    PS : Where can I found all the GUID such as
    "{00020905-0000-0000-C000-000000000046} ???
    Thanks again



    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > Yes, should work with all versions of Word as it does Major:=0, Minor:=0.
    >
    >
    > To remove do this:
    >
    > Sub RemoveWordReference()
    >
    > RemoveReference "Word"
    >
    > End Sub
    >
    > Sub RemoveReference(strReference As String)
    >
    > On Error GoTo ERROROUT
    >
    > Dim R As Object
    >
    > For Each R In ThisWorkbook.VBProject.References
    > If R.Name = strReference Then
    > ThisWorkbook.VBProject.References.Remove R
    > Exit Sub
    > End If
    > Next
    >
    > ERROROUT:
    > On Error GoTo 0
    >
    > End Sub
    >
    > As it does Dim R As Object, rather than R as Reference you don't need a
    > reference to
    > Microsoft Visual Basic for Applications Extensibility.
    >
    >
    > RBS
    >
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > > Thanks a lot RB Smissaert !
    > >
    > > 1° - Does this procedure work if Word 2000 is installed as well with

    Word
    > > 2003 installed?
    > > 2° - I need to remove the reference because another VBA procedure (which
    > > doesn't uses Word) hangs if the reference is still there!
    > > Thanks again !
    > >
    > > Newbie
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    de
    > > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > >> Sub ActivateWordLibrary()
    > >>
    > >> Dim R
    > >>
    > >> On Error Resume Next
    > >>
    > >> 'no need to carry on if the Word Object Library is already there
    > >> '---------------------------------------------------------------
    > >> For Each R In ThisWorkbook.VBProject.References
    > >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > >> Exit Sub
    > >> End If
    > >> Next
    > >>
    > >> ThisWorkbook.VBProject.References.AddFromGuid _
    > >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > >> Major:=0, Minor:=0
    > >>
    > >> On Error GoTo 0
    > >>
    > >> End Sub
    > >>
    > >> You can remove the reference after, but not really neccessary.
    > >>
    > >> RBS
    > >>
    > >>
    > >> "Newbie" <newbie@libre.fr> wrote in message
    > >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > >> > Hello
    > >> > As the Late Binding seems very slow, I would like to create a

    reference
    > > on
    > >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > >> > others
    > >> > are
    > >> > running Office 2003.
    > >> > How can i create a Reference for Microsoft Word 9.0 or for Microsoft

    > > Word
    > >> > 11.0 (2000 and 2003)?
    > >> > How to remove this Reference after the VBA procedure has done its

    work?
    > >> > Thanks
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  6. #6
    Tom Ogilvy
    Guest

    Re: Dynamic References to Word?

    Look in the registry although I suspect Bart set a reference and then
    queried the reference with VBA.

    from the immediate window as an example:

    ? thisworkbook.vbProject.references(2).Guid
    {00020813-0000-0000-C000-000000000046}
    ? thisworkbook.VBProject.References(2).Name
    Excel
    ? thisworkbook.vbProject.references(7).Guid
    {00020905-0000-0000-C000-000000000046}
    ? thisworkbook.VBProject.References(7).Name
    Word


    --
    Regards,
    Tom Ogilvy

    "Newbie" <newbie@libre.fr> wrote in message
    news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > Thanks a lot RBS !
    > Very useful!
    > PS : Where can I found all the GUID such as
    > "{00020905-0000-0000-C000-000000000046} ???
    > Thanks again
    >
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > > Yes, should work with all versions of Word as it does Major:=0,

    Minor:=0.
    > >
    > >
    > > To remove do this:
    > >
    > > Sub RemoveWordReference()
    > >
    > > RemoveReference "Word"
    > >
    > > End Sub
    > >
    > > Sub RemoveReference(strReference As String)
    > >
    > > On Error GoTo ERROROUT
    > >
    > > Dim R As Object
    > >
    > > For Each R In ThisWorkbook.VBProject.References
    > > If R.Name = strReference Then
    > > ThisWorkbook.VBProject.References.Remove R
    > > Exit Sub
    > > End If
    > > Next
    > >
    > > ERROROUT:
    > > On Error GoTo 0
    > >
    > > End Sub
    > >
    > > As it does Dim R As Object, rather than R as Reference you don't need a
    > > reference to
    > > Microsoft Visual Basic for Applications Extensibility.
    > >
    > >
    > > RBS
    > >
    > >
    > > "Newbie" <newbie@libre.fr> wrote in message
    > > news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > > > Thanks a lot RB Smissaert !
    > > >
    > > > 1° - Does this procedure work if Word 2000 is installed as well with

    > Word
    > > > 2003 installed?
    > > > 2° - I need to remove the reference because another VBA procedure

    (which
    > > > doesn't uses Word) hangs if the reference is still there!
    > > > Thanks again !
    > > >
    > > > Newbie
    > > >
    > > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    message
    > de
    > > > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > > >> Sub ActivateWordLibrary()
    > > >>
    > > >> Dim R
    > > >>
    > > >> On Error Resume Next
    > > >>
    > > >> 'no need to carry on if the Word Object Library is already there
    > > >> '---------------------------------------------------------------
    > > >> For Each R In ThisWorkbook.VBProject.References
    > > >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > > >> Exit Sub
    > > >> End If
    > > >> Next
    > > >>
    > > >> ThisWorkbook.VBProject.References.AddFromGuid _
    > > >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > > >> Major:=0, Minor:=0
    > > >>
    > > >> On Error GoTo 0
    > > >>
    > > >> End Sub
    > > >>
    > > >> You can remove the reference after, but not really neccessary.
    > > >>
    > > >> RBS
    > > >>
    > > >>
    > > >> "Newbie" <newbie@libre.fr> wrote in message
    > > >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > > >> > Hello
    > > >> > As the Late Binding seems very slow, I would like to create a

    > reference
    > > > on
    > > >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > > >> > others
    > > >> > are
    > > >> > running Office 2003.
    > > >> > How can i create a Reference for Microsoft Word 9.0 or for

    Microsoft
    > > > Word
    > > >> > 11.0 (2000 and 2003)?
    > > >> > How to remove this Reference after the VBA procedure has done its

    > work?
    > > >> > Thanks
    > > >> >
    > > >> >
    > > >>
    > > >
    > > >

    > >

    >
    >




  7. #7
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    The way to get those GUID's is like this:
    First set the reference manually in the VBE under Tools, References.
    Then run code like this:


    Sub GetLibraryGUID()

    Dim c As Byte
    Dim myCheck As Long
    Dim P As Boolean
    Dim rng As Range
    Dim i As Byte

    c = ActiveWorkbook.VBProject.References.Count

    On Error Resume Next
    Dim Message, Title, Default, T As Single
    Message = "NUMBER ?" & Chr(13) & "________"
    Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    Default = c
    T = InputBox(Message, Title, Default, 3500, 3500)

    If Not T Mod 1 = 0 Then
    Exit Sub
    End If

    If T < 1 Or T > c Then
    Exit Sub
    End If

    MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _
    "MAJOR : " & _
    ActiveWorkbook.VBProject.References.Item(T).Major & _
    vbCrLf & vbCrLf & "MINOR : " & _
    ActiveWorkbook.VBProject.References.Item(T).Minor & _
    vbCrLf & vbCrLf & _
    "GUID ( " & T & " ) : " & _
    ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    " REFERENCES GUID : ITEM " & T

    myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    vbYesNo, " GetLibraryGUID")

    If myCheck = vbNo Then
    Exit Sub
    End If

    If ActiveSheet.ProtectContents = True Then
    P = True
    ActiveSheet.Unprotect
    Else
    P = False
    End If

    Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select

    For Each rng In Selection.Cells
    If Not IsEmpty(rng) Then
    i = i + 1
    End If
    Next

    If i > 0 Then
    myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    vbYesNo, " GetLibraryGUID")
    If myCheck = vbNo Then
    Exit Sub
    End If
    End If

    On Error Resume Next
    ActiveCell.Value = "NAME :"
    ActiveCell.Offset(1, 0).Value = "MAJOR :"
    ActiveCell.Offset(2, 0).Value = "MINOR :"
    ActiveCell.Offset(3, 0).Value = "GUID :"
    ActiveCell.Offset(0, 1).Value = _
    ActiveWorkbook.VBProject.References(T).Name
    ActiveCell.Offset(1, 1).Value = _
    ActiveWorkbook.VBProject.References.Item(T).Major
    ActiveCell.Offset(2, 1).Value = _
    ActiveWorkbook.VBProject.References.Item(T).Minor
    ActiveCell.Offset(3, 1).Value = _
    ActiveWorkbook.VBProject.References.Item(T).GUID

    If P = True Then
    ActiveSheet.Protect
    End If

    End Sub

    That will give you all the information you need.
    Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where you
    specified a higher version than the one on the user's machine.


    RBS


    "Newbie" <newbie@libre.fr> wrote in message
    news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > Thanks a lot RBS !
    > Very useful!
    > PS : Where can I found all the GUID such as
    > "{00020905-0000-0000-C000-000000000046} ???
    > Thanks again
    >
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    >> Yes, should work with all versions of Word as it does Major:=0, Minor:=0.
    >>
    >>
    >> To remove do this:
    >>
    >> Sub RemoveWordReference()
    >>
    >> RemoveReference "Word"
    >>
    >> End Sub
    >>
    >> Sub RemoveReference(strReference As String)
    >>
    >> On Error GoTo ERROROUT
    >>
    >> Dim R As Object
    >>
    >> For Each R In ThisWorkbook.VBProject.References
    >> If R.Name = strReference Then
    >> ThisWorkbook.VBProject.References.Remove R
    >> Exit Sub
    >> End If
    >> Next
    >>
    >> ERROROUT:
    >> On Error GoTo 0
    >>
    >> End Sub
    >>
    >> As it does Dim R As Object, rather than R as Reference you don't need a
    >> reference to
    >> Microsoft Visual Basic for Applications Extensibility.
    >>
    >>
    >> RBS
    >>
    >>
    >> "Newbie" <newbie@libre.fr> wrote in message
    >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    >> > Thanks a lot RB Smissaert !
    >> >
    >> > 1° - Does this procedure work if Word 2000 is installed as well with

    > Word
    >> > 2003 installed?
    >> > 2° - I need to remove the reference because another VBA procedure
    >> > (which
    >> > doesn't uses Word) hangs if the reference is still there!
    >> > Thanks again !
    >> >
    >> > Newbie
    >> >
    >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    > de
    >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> >> Sub ActivateWordLibrary()
    >> >>
    >> >> Dim R
    >> >>
    >> >> On Error Resume Next
    >> >>
    >> >> 'no need to carry on if the Word Object Library is already there
    >> >> '---------------------------------------------------------------
    >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    >> >> Exit Sub
    >> >> End If
    >> >> Next
    >> >>
    >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> >> Major:=0, Minor:=0
    >> >>
    >> >> On Error GoTo 0
    >> >>
    >> >> End Sub
    >> >>
    >> >> You can remove the reference after, but not really neccessary.
    >> >>
    >> >> RBS
    >> >>
    >> >>
    >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> >> > Hello
    >> >> > As the Late Binding seems very slow, I would like to create a

    > reference
    >> > on
    >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    >> >> > others
    >> >> > are
    >> >> > running Office 2003.
    >> >> > How can i create a Reference for Microsoft Word 9.0 or for Microsoft
    >> > Word
    >> >> > 11.0 (2000 and 2003)?
    >> >> > How to remove this Reference after the VBA procedure has done its

    > work?
    >> >> > Thanks
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  8. #8
    Newbie
    Guest

    Re: Dynamic References to Word?

    Thanks a lot Tom !


    "Tom Ogilvy" <twogilvy@msn.com> a écrit dans le message de
    news:utJlQs0WGHA.4652@TK2MSFTNGP04.phx.gbl...
    > Look in the registry although I suspect Bart set a reference and then
    > queried the reference with VBA.
    >
    > from the immediate window as an example:
    >
    > ? thisworkbook.vbProject.references(2).Guid
    > {00020813-0000-0000-C000-000000000046}
    > ? thisworkbook.VBProject.References(2).Name
    > Excel
    > ? thisworkbook.vbProject.references(7).Guid
    > {00020905-0000-0000-C000-000000000046}
    > ? thisworkbook.VBProject.References(7).Name
    > Word
    >
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > > Thanks a lot RBS !
    > > Very useful!
    > > PS : Where can I found all the GUID such as
    > > "{00020905-0000-0000-C000-000000000046} ???
    > > Thanks again
    > >
    > >
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    de
    > > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > > > Yes, should work with all versions of Word as it does Major:=0,

    > Minor:=0.
    > > >
    > > >
    > > > To remove do this:
    > > >
    > > > Sub RemoveWordReference()
    > > >
    > > > RemoveReference "Word"
    > > >
    > > > End Sub
    > > >
    > > > Sub RemoveReference(strReference As String)
    > > >
    > > > On Error GoTo ERROROUT
    > > >
    > > > Dim R As Object
    > > >
    > > > For Each R In ThisWorkbook.VBProject.References
    > > > If R.Name = strReference Then
    > > > ThisWorkbook.VBProject.References.Remove R
    > > > Exit Sub
    > > > End If
    > > > Next
    > > >
    > > > ERROROUT:
    > > > On Error GoTo 0
    > > >
    > > > End Sub
    > > >
    > > > As it does Dim R As Object, rather than R as Reference you don't need

    a
    > > > reference to
    > > > Microsoft Visual Basic for Applications Extensibility.
    > > >
    > > >
    > > > RBS
    > > >
    > > >
    > > > "Newbie" <newbie@libre.fr> wrote in message
    > > > news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > > > > Thanks a lot RB Smissaert !
    > > > >
    > > > > 1° - Does this procedure work if Word 2000 is installed as well with

    > > Word
    > > > > 2003 installed?
    > > > > 2° - I need to remove the reference because another VBA procedure

    > (which
    > > > > doesn't uses Word) hangs if the reference is still there!
    > > > > Thanks again !
    > > > >
    > > > > Newbie
    > > > >
    > > > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > message
    > > de
    > > > > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > > > >> Sub ActivateWordLibrary()
    > > > >>
    > > > >> Dim R
    > > > >>
    > > > >> On Error Resume Next
    > > > >>
    > > > >> 'no need to carry on if the Word Object Library is already

    there
    > > > >>

    '---------------------------------------------------------------
    > > > >> For Each R In ThisWorkbook.VBProject.References
    > > > >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > > > >> Exit Sub
    > > > >> End If
    > > > >> Next
    > > > >>
    > > > >> ThisWorkbook.VBProject.References.AddFromGuid _
    > > > >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > > > >> Major:=0, Minor:=0
    > > > >>
    > > > >> On Error GoTo 0
    > > > >>
    > > > >> End Sub
    > > > >>
    > > > >> You can remove the reference after, but not really neccessary.
    > > > >>
    > > > >> RBS
    > > > >>
    > > > >>
    > > > >> "Newbie" <newbie@libre.fr> wrote in message
    > > > >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > > > >> > Hello
    > > > >> > As the Late Binding seems very slow, I would like to create a

    > > reference
    > > > > on
    > > > >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > > > >> > others
    > > > >> > are
    > > > >> > running Office 2003.
    > > > >> > How can i create a Reference for Microsoft Word 9.0 or for

    > Microsoft
    > > > > Word
    > > > >> > 11.0 (2000 and 2003)?
    > > > >> > How to remove this Reference after the VBA procedure has done its

    > > work?
    > > > >> > Thanks
    > > > >> >
    > > > >> >
    > > > >>
    > > > >
    > > > >
    > > >

    > >
    > >

    >
    >




  9. #9
    Newbie
    Guest

    Re: Dynamic References to Word?

    Wonderful indeed !
    Thanks a lot!

    Newbie

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    > The way to get those GUID's is like this:
    > First set the reference manually in the VBE under Tools, References.
    > Then run code like this:
    >
    >
    > Sub GetLibraryGUID()
    >
    > Dim c As Byte
    > Dim myCheck As Long
    > Dim P As Boolean
    > Dim rng As Range
    > Dim i As Byte
    >
    > c = ActiveWorkbook.VBProject.References.Count
    >
    > On Error Resume Next
    > Dim Message, Title, Default, T As Single
    > Message = "NUMBER ?" & Chr(13) & "________"
    > Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    > Default = c
    > T = InputBox(Message, Title, Default, 3500, 3500)
    >
    > If Not T Mod 1 = 0 Then
    > Exit Sub
    > End If
    >
    > If T < 1 Or T > c Then
    > Exit Sub
    > End If
    >
    > MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    > ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf &

    _
    > "MAJOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Major & _
    > vbCrLf & vbCrLf & "MINOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Minor & _
    > vbCrLf & vbCrLf & _
    > "GUID ( " & T & " ) : " & _
    > ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    > " REFERENCES GUID : ITEM " & T
    >
    > myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    > vbYesNo, " GetLibraryGUID")
    >
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    >
    > If ActiveSheet.ProtectContents = True Then
    > P = True
    > ActiveSheet.Unprotect
    > Else
    > P = False
    > End If
    >
    > Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    > Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >
    > For Each rng In Selection.Cells
    > If Not IsEmpty(rng) Then
    > i = i + 1
    > End If
    > Next
    >
    > If i > 0 Then
    > myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    > vbYesNo, " GetLibraryGUID")
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    > End If
    >
    > On Error Resume Next
    > ActiveCell.Value = "NAME :"
    > ActiveCell.Offset(1, 0).Value = "MAJOR :"
    > ActiveCell.Offset(2, 0).Value = "MINOR :"
    > ActiveCell.Offset(3, 0).Value = "GUID :"
    > ActiveCell.Offset(0, 1).Value = _
    > ActiveWorkbook.VBProject.References(T).Name
    > ActiveCell.Offset(1, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Major
    > ActiveCell.Offset(2, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Minor
    > ActiveCell.Offset(3, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).GUID
    >
    > If P = True Then
    > ActiveSheet.Protect
    > End If
    >
    > End Sub
    >
    > That will give you all the information you need.
    > Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    you
    > specified a higher version than the one on the user's machine.
    >
    >
    > RBS
    >
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > > Thanks a lot RBS !
    > > Very useful!
    > > PS : Where can I found all the GUID such as
    > > "{00020905-0000-0000-C000-000000000046} ???
    > > Thanks again
    > >
    > >
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    de
    > > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > >> Yes, should work with all versions of Word as it does Major:=0,

    Minor:=0.
    > >>
    > >>
    > >> To remove do this:
    > >>
    > >> Sub RemoveWordReference()
    > >>
    > >> RemoveReference "Word"
    > >>
    > >> End Sub
    > >>
    > >> Sub RemoveReference(strReference As String)
    > >>
    > >> On Error GoTo ERROROUT
    > >>
    > >> Dim R As Object
    > >>
    > >> For Each R In ThisWorkbook.VBProject.References
    > >> If R.Name = strReference Then
    > >> ThisWorkbook.VBProject.References.Remove R
    > >> Exit Sub
    > >> End If
    > >> Next
    > >>
    > >> ERROROUT:
    > >> On Error GoTo 0
    > >>
    > >> End Sub
    > >>
    > >> As it does Dim R As Object, rather than R as Reference you don't need a
    > >> reference to
    > >> Microsoft Visual Basic for Applications Extensibility.
    > >>
    > >>
    > >> RBS
    > >>
    > >>
    > >> "Newbie" <newbie@libre.fr> wrote in message
    > >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > >> > Thanks a lot RB Smissaert !
    > >> >
    > >> > 1° - Does this procedure work if Word 2000 is installed as well with

    > > Word
    > >> > 2003 installed?
    > >> > 2° - I need to remove the reference because another VBA procedure
    > >> > (which
    > >> > doesn't uses Word) hangs if the reference is still there!
    > >> > Thanks again !
    > >> >
    > >> > Newbie
    > >> >
    > >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    message
    > > de
    > >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > >> >> Sub ActivateWordLibrary()
    > >> >>
    > >> >> Dim R
    > >> >>
    > >> >> On Error Resume Next
    > >> >>
    > >> >> 'no need to carry on if the Word Object Library is already there
    > >> >> '---------------------------------------------------------------
    > >> >> For Each R In ThisWorkbook.VBProject.References
    > >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > >> >> Exit Sub
    > >> >> End If
    > >> >> Next
    > >> >>
    > >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    > >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > >> >> Major:=0, Minor:=0
    > >> >>
    > >> >> On Error GoTo 0
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> You can remove the reference after, but not really neccessary.
    > >> >>
    > >> >> RBS
    > >> >>
    > >> >>
    > >> >> "Newbie" <newbie@libre.fr> wrote in message
    > >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > >> >> > Hello
    > >> >> > As the Late Binding seems very slow, I would like to create a

    > > reference
    > >> > on
    > >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > >> >> > others
    > >> >> > are
    > >> >> > running Office 2003.
    > >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    Microsoft
    > >> > Word
    > >> >> > 11.0 (2000 and 2003)?
    > >> >> > How to remove this Reference after the VBA procedure has done its

    > > work?
    > >> >> > Thanks
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  10. #10
    Newbie
    Guest

    Re: Dynamic References to Word?

    RBS,
    I tried your macro but on the first line, c =
    ActiveWorkbook.VBProject.References.Count
    it bumps on the "Error # '1004' : the programming access to the Visual Basic
    project is not reliable"
    What is my problem?
    Thanks again!



    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    > The way to get those GUID's is like this:
    > First set the reference manually in the VBE under Tools, References.
    > Then run code like this:
    >
    >
    > Sub GetLibraryGUID()
    >
    > Dim c As Byte
    > Dim myCheck As Long
    > Dim P As Boolean
    > Dim rng As Range
    > Dim i As Byte
    >
    > c = ActiveWorkbook.VBProject.References.Count
    >
    > On Error Resume Next
    > Dim Message, Title, Default, T As Single
    > Message = "NUMBER ?" & Chr(13) & "________"
    > Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    > Default = c
    > T = InputBox(Message, Title, Default, 3500, 3500)
    >
    > If Not T Mod 1 = 0 Then
    > Exit Sub
    > End If
    >
    > If T < 1 Or T > c Then
    > Exit Sub
    > End If
    >
    > MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    > ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf &

    _
    > "MAJOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Major & _
    > vbCrLf & vbCrLf & "MINOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Minor & _
    > vbCrLf & vbCrLf & _
    > "GUID ( " & T & " ) : " & _
    > ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    > " REFERENCES GUID : ITEM " & T
    >
    > myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    > vbYesNo, " GetLibraryGUID")
    >
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    >
    > If ActiveSheet.ProtectContents = True Then
    > P = True
    > ActiveSheet.Unprotect
    > Else
    > P = False
    > End If
    >
    > Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    > Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >
    > For Each rng In Selection.Cells
    > If Not IsEmpty(rng) Then
    > i = i + 1
    > End If
    > Next
    >
    > If i > 0 Then
    > myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    > vbYesNo, " GetLibraryGUID")
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    > End If
    >
    > On Error Resume Next
    > ActiveCell.Value = "NAME :"
    > ActiveCell.Offset(1, 0).Value = "MAJOR :"
    > ActiveCell.Offset(2, 0).Value = "MINOR :"
    > ActiveCell.Offset(3, 0).Value = "GUID :"
    > ActiveCell.Offset(0, 1).Value = _
    > ActiveWorkbook.VBProject.References(T).Name
    > ActiveCell.Offset(1, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Major
    > ActiveCell.Offset(2, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Minor
    > ActiveCell.Offset(3, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).GUID
    >
    > If P = True Then
    > ActiveSheet.Protect
    > End If
    >
    > End Sub
    >
    > That will give you all the information you need.
    > Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    you
    > specified a higher version than the one on the user's machine.
    >
    >
    > RBS
    >
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > > Thanks a lot RBS !
    > > Very useful!
    > > PS : Where can I found all the GUID such as
    > > "{00020905-0000-0000-C000-000000000046} ???
    > > Thanks again
    > >
    > >
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    de
    > > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > >> Yes, should work with all versions of Word as it does Major:=0,

    Minor:=0.
    > >>
    > >>
    > >> To remove do this:
    > >>
    > >> Sub RemoveWordReference()
    > >>
    > >> RemoveReference "Word"
    > >>
    > >> End Sub
    > >>
    > >> Sub RemoveReference(strReference As String)
    > >>
    > >> On Error GoTo ERROROUT
    > >>
    > >> Dim R As Object
    > >>
    > >> For Each R In ThisWorkbook.VBProject.References
    > >> If R.Name = strReference Then
    > >> ThisWorkbook.VBProject.References.Remove R
    > >> Exit Sub
    > >> End If
    > >> Next
    > >>
    > >> ERROROUT:
    > >> On Error GoTo 0
    > >>
    > >> End Sub
    > >>
    > >> As it does Dim R As Object, rather than R as Reference you don't need a
    > >> reference to
    > >> Microsoft Visual Basic for Applications Extensibility.
    > >>
    > >>
    > >> RBS
    > >>
    > >>
    > >> "Newbie" <newbie@libre.fr> wrote in message
    > >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > >> > Thanks a lot RB Smissaert !
    > >> >
    > >> > 1° - Does this procedure work if Word 2000 is installed as well with

    > > Word
    > >> > 2003 installed?
    > >> > 2° - I need to remove the reference because another VBA procedure
    > >> > (which
    > >> > doesn't uses Word) hangs if the reference is still there!
    > >> > Thanks again !
    > >> >
    > >> > Newbie
    > >> >
    > >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    message
    > > de
    > >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > >> >> Sub ActivateWordLibrary()
    > >> >>
    > >> >> Dim R
    > >> >>
    > >> >> On Error Resume Next
    > >> >>
    > >> >> 'no need to carry on if the Word Object Library is already there
    > >> >> '---------------------------------------------------------------
    > >> >> For Each R In ThisWorkbook.VBProject.References
    > >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > >> >> Exit Sub
    > >> >> End If
    > >> >> Next
    > >> >>
    > >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    > >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > >> >> Major:=0, Minor:=0
    > >> >>
    > >> >> On Error GoTo 0
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> You can remove the reference after, but not really neccessary.
    > >> >>
    > >> >> RBS
    > >> >>
    > >> >>
    > >> >> "Newbie" <newbie@libre.fr> wrote in message
    > >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > >> >> > Hello
    > >> >> > As the Late Binding seems very slow, I would like to create a

    > > reference
    > >> > on
    > >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > >> >> > others
    > >> >> > are
    > >> >> > running Office 2003.
    > >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    Microsoft
    > >> > Word
    > >> >> > 11.0 (2000 and 2003)?
    > >> >> > How to remove this Reference after the VBA procedure has done its

    > > work?
    > >> >> > Thanks
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  11. #11
    Tom Ogilvy
    Guest

    Re: Dynamic References to Word?

    Just for interest, not sure what you gain with Byte, it is much shower than
    Long. I get it at about 16 times slower. (Integer is about 10 times
    slower). (and I don't think you are saving any space either - probably
    using the same amount of space although I haven't explored it).

    --
    Regards,
    Tom Ogilvy



    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    > The way to get those GUID's is like this:
    > First set the reference manually in the VBE under Tools, References.
    > Then run code like this:
    >
    >
    > Sub GetLibraryGUID()
    >
    > Dim c As Byte
    > Dim myCheck As Long
    > Dim P As Boolean
    > Dim rng As Range
    > Dim i As Byte
    >
    > c = ActiveWorkbook.VBProject.References.Count
    >
    > On Error Resume Next
    > Dim Message, Title, Default, T As Single
    > Message = "NUMBER ?" & Chr(13) & "________"
    > Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    > Default = c
    > T = InputBox(Message, Title, Default, 3500, 3500)
    >
    > If Not T Mod 1 = 0 Then
    > Exit Sub
    > End If
    >
    > If T < 1 Or T > c Then
    > Exit Sub
    > End If
    >
    > MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    > ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf &

    _
    > "MAJOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Major & _
    > vbCrLf & vbCrLf & "MINOR : " & _
    > ActiveWorkbook.VBProject.References.Item(T).Minor & _
    > vbCrLf & vbCrLf & _
    > "GUID ( " & T & " ) : " & _
    > ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    > " REFERENCES GUID : ITEM " & T
    >
    > myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    > vbYesNo, " GetLibraryGUID")
    >
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    >
    > If ActiveSheet.ProtectContents = True Then
    > P = True
    > ActiveSheet.Unprotect
    > Else
    > P = False
    > End If
    >
    > Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    > Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >
    > For Each rng In Selection.Cells
    > If Not IsEmpty(rng) Then
    > i = i + 1
    > End If
    > Next
    >
    > If i > 0 Then
    > myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    > vbYesNo, " GetLibraryGUID")
    > If myCheck = vbNo Then
    > Exit Sub
    > End If
    > End If
    >
    > On Error Resume Next
    > ActiveCell.Value = "NAME :"
    > ActiveCell.Offset(1, 0).Value = "MAJOR :"
    > ActiveCell.Offset(2, 0).Value = "MINOR :"
    > ActiveCell.Offset(3, 0).Value = "GUID :"
    > ActiveCell.Offset(0, 1).Value = _
    > ActiveWorkbook.VBProject.References(T).Name
    > ActiveCell.Offset(1, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Major
    > ActiveCell.Offset(2, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).Minor
    > ActiveCell.Offset(3, 1).Value = _
    > ActiveWorkbook.VBProject.References.Item(T).GUID
    >
    > If P = True Then
    > ActiveSheet.Protect
    > End If
    >
    > End Sub
    >
    > That will give you all the information you need.
    > Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    you
    > specified a higher version than the one on the user's machine.
    >
    >
    > RBS
    >
    >
    > "Newbie" <newbie@libre.fr> wrote in message
    > news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > > Thanks a lot RBS !
    > > Very useful!
    > > PS : Where can I found all the GUID such as
    > > "{00020905-0000-0000-C000-000000000046} ???
    > > Thanks again
    > >
    > >
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    de
    > > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > >> Yes, should work with all versions of Word as it does Major:=0,

    Minor:=0.
    > >>
    > >>
    > >> To remove do this:
    > >>
    > >> Sub RemoveWordReference()
    > >>
    > >> RemoveReference "Word"
    > >>
    > >> End Sub
    > >>
    > >> Sub RemoveReference(strReference As String)
    > >>
    > >> On Error GoTo ERROROUT
    > >>
    > >> Dim R As Object
    > >>
    > >> For Each R In ThisWorkbook.VBProject.References
    > >> If R.Name = strReference Then
    > >> ThisWorkbook.VBProject.References.Remove R
    > >> Exit Sub
    > >> End If
    > >> Next
    > >>
    > >> ERROROUT:
    > >> On Error GoTo 0
    > >>
    > >> End Sub
    > >>
    > >> As it does Dim R As Object, rather than R as Reference you don't need a
    > >> reference to
    > >> Microsoft Visual Basic for Applications Extensibility.
    > >>
    > >>
    > >> RBS
    > >>
    > >>
    > >> "Newbie" <newbie@libre.fr> wrote in message
    > >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > >> > Thanks a lot RB Smissaert !
    > >> >
    > >> > 1° - Does this procedure work if Word 2000 is installed as well with

    > > Word
    > >> > 2003 installed?
    > >> > 2° - I need to remove the reference because another VBA procedure
    > >> > (which
    > >> > doesn't uses Word) hangs if the reference is still there!
    > >> > Thanks again !
    > >> >
    > >> > Newbie
    > >> >
    > >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    message
    > > de
    > >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > >> >> Sub ActivateWordLibrary()
    > >> >>
    > >> >> Dim R
    > >> >>
    > >> >> On Error Resume Next
    > >> >>
    > >> >> 'no need to carry on if the Word Object Library is already there
    > >> >> '---------------------------------------------------------------
    > >> >> For Each R In ThisWorkbook.VBProject.References
    > >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > >> >> Exit Sub
    > >> >> End If
    > >> >> Next
    > >> >>
    > >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    > >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > >> >> Major:=0, Minor:=0
    > >> >>
    > >> >> On Error GoTo 0
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> You can remove the reference after, but not really neccessary.
    > >> >>
    > >> >> RBS
    > >> >>
    > >> >>
    > >> >> "Newbie" <newbie@libre.fr> wrote in message
    > >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > >> >> > Hello
    > >> >> > As the Late Binding seems very slow, I would like to create a

    > > reference
    > >> > on
    > >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    > >> >> > others
    > >> >> > are
    > >> >> > running Office 2003.
    > >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    Microsoft
    > >> > Word
    > >> >> > 11.0 (2000 and 2003)?
    > >> >> > How to remove this Reference after the VBA procedure has done its

    > > work?
    > >> >> > Thanks
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  12. #12
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    I am sure you are right. This is some old code I wrote many years ago.
    Would do it all a bit different now, including not doing all those upper
    case characters!

    RBS

    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23wJ6gS1WGHA.4652@TK2MSFTNGP04.phx.gbl...
    > Just for interest, not sure what you gain with Byte, it is much shower
    > than
    > Long. I get it at about 16 times slower. (Integer is about 10 times
    > slower). (and I don't think you are saving any space either - probably
    > using the same amount of space although I haven't explored it).
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    >> The way to get those GUID's is like this:
    >> First set the reference manually in the VBE under Tools, References.
    >> Then run code like this:
    >>
    >>
    >> Sub GetLibraryGUID()
    >>
    >> Dim c As Byte
    >> Dim myCheck As Long
    >> Dim P As Boolean
    >> Dim rng As Range
    >> Dim i As Byte
    >>
    >> c = ActiveWorkbook.VBProject.References.Count
    >>
    >> On Error Resume Next
    >> Dim Message, Title, Default, T As Single
    >> Message = "NUMBER ?" & Chr(13) & "________"
    >> Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    >> Default = c
    >> T = InputBox(Message, Title, Default, 3500, 3500)
    >>
    >> If Not T Mod 1 = 0 Then
    >> Exit Sub
    >> End If
    >>
    >> If T < 1 Or T > c Then
    >> Exit Sub
    >> End If
    >>
    >> MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    >> ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf
    >> &

    > _
    >> "MAJOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Major & _
    >> vbCrLf & vbCrLf & "MINOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor & _
    >> vbCrLf & vbCrLf & _
    >> "GUID ( " & T & " ) : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    >> " REFERENCES GUID : ITEM " & T
    >>
    >> myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    >> vbYesNo, " GetLibraryGUID")
    >>
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >>
    >> If ActiveSheet.ProtectContents = True Then
    >> P = True
    >> ActiveSheet.Unprotect
    >> Else
    >> P = False
    >> End If
    >>
    >> Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    >> Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >>
    >> For Each rng In Selection.Cells
    >> If Not IsEmpty(rng) Then
    >> i = i + 1
    >> End If
    >> Next
    >>
    >> If i > 0 Then
    >> myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    >> vbYesNo, " GetLibraryGUID")
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >> End If
    >>
    >> On Error Resume Next
    >> ActiveCell.Value = "NAME :"
    >> ActiveCell.Offset(1, 0).Value = "MAJOR :"
    >> ActiveCell.Offset(2, 0).Value = "MINOR :"
    >> ActiveCell.Offset(3, 0).Value = "GUID :"
    >> ActiveCell.Offset(0, 1).Value = _
    >> ActiveWorkbook.VBProject.References(T).Name
    >> ActiveCell.Offset(1, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Major
    >> ActiveCell.Offset(2, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor
    >> ActiveCell.Offset(3, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID
    >>
    >> If P = True Then
    >> ActiveSheet.Protect
    >> End If
    >>
    >> End Sub
    >>
    >> That will give you all the information you need.
    >> Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    > you
    >> specified a higher version than the one on the user's machine.
    >>
    >>
    >> RBS
    >>
    >>
    >> "Newbie" <newbie@libre.fr> wrote in message
    >> news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    >> > Thanks a lot RBS !
    >> > Very useful!
    >> > PS : Where can I found all the GUID such as
    >> > "{00020905-0000-0000-C000-000000000046} ???
    >> > Thanks again
    >> >
    >> >
    >> >
    >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    > de
    >> > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    >> >> Yes, should work with all versions of Word as it does Major:=0,

    > Minor:=0.
    >> >>
    >> >>
    >> >> To remove do this:
    >> >>
    >> >> Sub RemoveWordReference()
    >> >>
    >> >> RemoveReference "Word"
    >> >>
    >> >> End Sub
    >> >>
    >> >> Sub RemoveReference(strReference As String)
    >> >>
    >> >> On Error GoTo ERROROUT
    >> >>
    >> >> Dim R As Object
    >> >>
    >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> If R.Name = strReference Then
    >> >> ThisWorkbook.VBProject.References.Remove R
    >> >> Exit Sub
    >> >> End If
    >> >> Next
    >> >>
    >> >> ERROROUT:
    >> >> On Error GoTo 0
    >> >>
    >> >> End Sub
    >> >>
    >> >> As it does Dim R As Object, rather than R as Reference you don't need
    >> >> a
    >> >> reference to
    >> >> Microsoft Visual Basic for Applications Extensibility.
    >> >>
    >> >>
    >> >> RBS
    >> >>
    >> >>
    >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    >> >> > Thanks a lot RB Smissaert !
    >> >> >
    >> >> > 1° - Does this procedure work if Word 2000 is installed as well with
    >> > Word
    >> >> > 2003 installed?
    >> >> > 2° - I need to remove the reference because another VBA procedure
    >> >> > (which
    >> >> > doesn't uses Word) hangs if the reference is still there!
    >> >> > Thanks again !
    >> >> >
    >> >> > Newbie
    >> >> >
    >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > message
    >> > de
    >> >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> >> >> Sub ActivateWordLibrary()
    >> >> >>
    >> >> >> Dim R
    >> >> >>
    >> >> >> On Error Resume Next
    >> >> >>
    >> >> >> 'no need to carry on if the Word Object Library is already
    >> >> >> there
    >> >> >>
    >> >> >> '---------------------------------------------------------------
    >> >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    >> >> >> Exit Sub
    >> >> >> End If
    >> >> >> Next
    >> >> >>
    >> >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> >> >> Major:=0, Minor:=0
    >> >> >>
    >> >> >> On Error GoTo 0
    >> >> >>
    >> >> >> End Sub
    >> >> >>
    >> >> >> You can remove the reference after, but not really neccessary.
    >> >> >>
    >> >> >> RBS
    >> >> >>
    >> >> >>
    >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> >> >> > Hello
    >> >> >> > As the Late Binding seems very slow, I would like to create a
    >> > reference
    >> >> > on
    >> >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    >> >> >> > others
    >> >> >> > are
    >> >> >> > running Office 2003.
    >> >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    > Microsoft
    >> >> > Word
    >> >> >> > 11.0 (2000 and 2003)?
    >> >> >> > How to remove this Reference after the VBA procedure has done its
    >> > work?
    >> >> >> > Thanks
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  13. #13
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    You will have to lower security in Excel by doing:
    Tools, Macro, Security, Security level: Low or medium
    Trusted sources: tick both.

    RBS

    "Newbie" <newbie@libre.fr> wrote in message
    news:Om8IRL1WGHA.3864@TK2MSFTNGP04.phx.gbl...
    > RBS,
    > I tried your macro but on the first line, c =
    > ActiveWorkbook.VBProject.References.Count
    > it bumps on the "Error # '1004' : the programming access to the Visual
    > Basic
    > project is not reliable"
    > What is my problem?
    > Thanks again!
    >
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    > news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    >> The way to get those GUID's is like this:
    >> First set the reference manually in the VBE under Tools, References.
    >> Then run code like this:
    >>
    >>
    >> Sub GetLibraryGUID()
    >>
    >> Dim c As Byte
    >> Dim myCheck As Long
    >> Dim P As Boolean
    >> Dim rng As Range
    >> Dim i As Byte
    >>
    >> c = ActiveWorkbook.VBProject.References.Count
    >>
    >> On Error Resume Next
    >> Dim Message, Title, Default, T As Single
    >> Message = "NUMBER ?" & Chr(13) & "________"
    >> Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    >> Default = c
    >> T = InputBox(Message, Title, Default, 3500, 3500)
    >>
    >> If Not T Mod 1 = 0 Then
    >> Exit Sub
    >> End If
    >>
    >> If T < 1 Or T > c Then
    >> Exit Sub
    >> End If
    >>
    >> MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    >> ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf
    >> &

    > _
    >> "MAJOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Major & _
    >> vbCrLf & vbCrLf & "MINOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor & _
    >> vbCrLf & vbCrLf & _
    >> "GUID ( " & T & " ) : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    >> " REFERENCES GUID : ITEM " & T
    >>
    >> myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    >> vbYesNo, " GetLibraryGUID")
    >>
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >>
    >> If ActiveSheet.ProtectContents = True Then
    >> P = True
    >> ActiveSheet.Unprotect
    >> Else
    >> P = False
    >> End If
    >>
    >> Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    >> Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >>
    >> For Each rng In Selection.Cells
    >> If Not IsEmpty(rng) Then
    >> i = i + 1
    >> End If
    >> Next
    >>
    >> If i > 0 Then
    >> myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    >> vbYesNo, " GetLibraryGUID")
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >> End If
    >>
    >> On Error Resume Next
    >> ActiveCell.Value = "NAME :"
    >> ActiveCell.Offset(1, 0).Value = "MAJOR :"
    >> ActiveCell.Offset(2, 0).Value = "MINOR :"
    >> ActiveCell.Offset(3, 0).Value = "GUID :"
    >> ActiveCell.Offset(0, 1).Value = _
    >> ActiveWorkbook.VBProject.References(T).Name
    >> ActiveCell.Offset(1, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Major
    >> ActiveCell.Offset(2, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor
    >> ActiveCell.Offset(3, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID
    >>
    >> If P = True Then
    >> ActiveSheet.Protect
    >> End If
    >>
    >> End Sub
    >>
    >> That will give you all the information you need.
    >> Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    > you
    >> specified a higher version than the one on the user's machine.
    >>
    >>
    >> RBS
    >>
    >>
    >> "Newbie" <newbie@libre.fr> wrote in message
    >> news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    >> > Thanks a lot RBS !
    >> > Very useful!
    >> > PS : Where can I found all the GUID such as
    >> > "{00020905-0000-0000-C000-000000000046} ???
    >> > Thanks again
    >> >
    >> >
    >> >
    >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    > de
    >> > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    >> >> Yes, should work with all versions of Word as it does Major:=0,

    > Minor:=0.
    >> >>
    >> >>
    >> >> To remove do this:
    >> >>
    >> >> Sub RemoveWordReference()
    >> >>
    >> >> RemoveReference "Word"
    >> >>
    >> >> End Sub
    >> >>
    >> >> Sub RemoveReference(strReference As String)
    >> >>
    >> >> On Error GoTo ERROROUT
    >> >>
    >> >> Dim R As Object
    >> >>
    >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> If R.Name = strReference Then
    >> >> ThisWorkbook.VBProject.References.Remove R
    >> >> Exit Sub
    >> >> End If
    >> >> Next
    >> >>
    >> >> ERROROUT:
    >> >> On Error GoTo 0
    >> >>
    >> >> End Sub
    >> >>
    >> >> As it does Dim R As Object, rather than R as Reference you don't need
    >> >> a
    >> >> reference to
    >> >> Microsoft Visual Basic for Applications Extensibility.
    >> >>
    >> >>
    >> >> RBS
    >> >>
    >> >>
    >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    >> >> > Thanks a lot RB Smissaert !
    >> >> >
    >> >> > 1° - Does this procedure work if Word 2000 is installed as well with
    >> > Word
    >> >> > 2003 installed?
    >> >> > 2° - I need to remove the reference because another VBA procedure
    >> >> > (which
    >> >> > doesn't uses Word) hangs if the reference is still there!
    >> >> > Thanks again !
    >> >> >
    >> >> > Newbie
    >> >> >
    >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > message
    >> > de
    >> >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> >> >> Sub ActivateWordLibrary()
    >> >> >>
    >> >> >> Dim R
    >> >> >>
    >> >> >> On Error Resume Next
    >> >> >>
    >> >> >> 'no need to carry on if the Word Object Library is already
    >> >> >> there
    >> >> >>
    >> >> >> '---------------------------------------------------------------
    >> >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    >> >> >> Exit Sub
    >> >> >> End If
    >> >> >> Next
    >> >> >>
    >> >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> >> >> Major:=0, Minor:=0
    >> >> >>
    >> >> >> On Error GoTo 0
    >> >> >>
    >> >> >> End Sub
    >> >> >>
    >> >> >> You can remove the reference after, but not really neccessary.
    >> >> >>
    >> >> >> RBS
    >> >> >>
    >> >> >>
    >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> >> >> > Hello
    >> >> >> > As the Late Binding seems very slow, I would like to create a
    >> > reference
    >> >> > on
    >> >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    >> >> >> > others
    >> >> >> > are
    >> >> >> > running Office 2003.
    >> >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    > Microsoft
    >> >> > Word
    >> >> >> > 11.0 (2000 and 2003)?
    >> >> >> > How to remove this Reference after the VBA procedure has done its
    >> > work?
    >> >> >> > Thanks
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  14. #14
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    This will all do it a bit better.
    Will dump all the references of all open projects:

    Sub ListExcelReferences()

    'to list all the references in Excel
    '-----------------------------------

    Dim i As Long
    Dim n As Long
    Dim iRefCount As Long
    Dim VBProj As Object

    Cells.Clear

    Cells(1).Value = "Project name"
    Cells(2).Value = "Project file"
    Cells(3).Value = "Reference Name"
    Cells(4).Value = "Description"
    Cells(5).Value = "FullPath"
    Cells(6).Value = "GUID"
    Cells(7).Value = "Major"
    Cells(8).Value = "Minor"

    On Error Resume Next 'as an un-saved workbook has no filename yet

    For Each VBProj In Application.VBE.VBProjects
    n = n + 1
    With VBProj
    iRefCount = .References.Count
    With .References
    For i = 1 To iRefCount
    n = n + 1
    If i = 1 Then
    Cells(n, 1).Value = VBProj.Name
    Cells(n, 2).Value = VBProj.Filename
    If Err.Number = 76 Then 'Path not found
    Cells(n, 2).Value = "Project not saved yet"
    Err.Clear
    End If
    End If
    Cells(n, 3).Value = .Item(i).Name
    Cells(n, 4).Value = .Item(i).Description
    Cells(n, 5).Value = .Item(i).FullPath
    Cells(n, 6).Value = .Item(i).GUID
    Cells(n, 7).Value = .Item(i).Major
    Cells(n, 8).Value = .Item(i).Minor
    Next i
    End With
    End With
    Next

    On Error GoTo 0

    ThinRightBorder Range(Cells(2), Cells(n, 2))
    Range(Cells(1), Cells(8)).Font.Bold = True
    MediumBorder Range(Cells(1), Cells(8))
    Range(Cells(1), Cells(n, 8)).Columns.AutoFit

    End Sub

    Sub MediumBorder(rng As Range, Optional wsh As Worksheet)

    'puts a medium border around the passed range
    '--------------------------------------------

    Dim Sh As Worksheet

    If wsh Is Nothing Then
    Set Sh = ActiveWorkbook.ActiveSheet
    Else
    Set Sh = wsh
    End If

    With Sh.Range(rng.Address)
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    End With

    End Sub

    Sub ThinRightBorder(rng As Range)

    With rng
    With .Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End With

    End Sub


    RBS


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23wJ6gS1WGHA.4652@TK2MSFTNGP04.phx.gbl...
    > Just for interest, not sure what you gain with Byte, it is much shower
    > than
    > Long. I get it at about 16 times slower. (Integer is about 10 times
    > slower). (and I don't think you are saving any space either - probably
    > using the same amount of space although I haven't explored it).
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    >> The way to get those GUID's is like this:
    >> First set the reference manually in the VBE under Tools, References.
    >> Then run code like this:
    >>
    >>
    >> Sub GetLibraryGUID()
    >>
    >> Dim c As Byte
    >> Dim myCheck As Long
    >> Dim P As Boolean
    >> Dim rng As Range
    >> Dim i As Byte
    >>
    >> c = ActiveWorkbook.VBProject.References.Count
    >>
    >> On Error Resume Next
    >> Dim Message, Title, Default, T As Single
    >> Message = "NUMBER ?" & Chr(13) & "________"
    >> Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    >> Default = c
    >> T = InputBox(Message, Title, Default, 3500, 3500)
    >>
    >> If Not T Mod 1 = 0 Then
    >> Exit Sub
    >> End If
    >>
    >> If T < 1 Or T > c Then
    >> Exit Sub
    >> End If
    >>
    >> MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    >> ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf
    >> &

    > _
    >> "MAJOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Major & _
    >> vbCrLf & vbCrLf & "MINOR : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor & _
    >> vbCrLf & vbCrLf & _
    >> "GUID ( " & T & " ) : " & _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    >> " REFERENCES GUID : ITEM " & T
    >>
    >> myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    >> vbYesNo, " GetLibraryGUID")
    >>
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >>
    >> If ActiveSheet.ProtectContents = True Then
    >> P = True
    >> ActiveSheet.Unprotect
    >> Else
    >> P = False
    >> End If
    >>
    >> Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    >> Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >>
    >> For Each rng In Selection.Cells
    >> If Not IsEmpty(rng) Then
    >> i = i + 1
    >> End If
    >> Next
    >>
    >> If i > 0 Then
    >> myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    >> vbYesNo, " GetLibraryGUID")
    >> If myCheck = vbNo Then
    >> Exit Sub
    >> End If
    >> End If
    >>
    >> On Error Resume Next
    >> ActiveCell.Value = "NAME :"
    >> ActiveCell.Offset(1, 0).Value = "MAJOR :"
    >> ActiveCell.Offset(2, 0).Value = "MINOR :"
    >> ActiveCell.Offset(3, 0).Value = "GUID :"
    >> ActiveCell.Offset(0, 1).Value = _
    >> ActiveWorkbook.VBProject.References(T).Name
    >> ActiveCell.Offset(1, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Major
    >> ActiveCell.Offset(2, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).Minor
    >> ActiveCell.Offset(3, 1).Value = _
    >> ActiveWorkbook.VBProject.References.Item(T).GUID
    >>
    >> If P = True Then
    >> ActiveSheet.Protect
    >> End If
    >>
    >> End Sub
    >>
    >> That will give you all the information you need.
    >> Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem where

    > you
    >> specified a higher version than the one on the user's machine.
    >>
    >>
    >> RBS
    >>
    >>
    >> "Newbie" <newbie@libre.fr> wrote in message
    >> news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    >> > Thanks a lot RBS !
    >> > Very useful!
    >> > PS : Where can I found all the GUID such as
    >> > "{00020905-0000-0000-C000-000000000046} ???
    >> > Thanks again
    >> >
    >> >
    >> >
    >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message

    > de
    >> > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    >> >> Yes, should work with all versions of Word as it does Major:=0,

    > Minor:=0.
    >> >>
    >> >>
    >> >> To remove do this:
    >> >>
    >> >> Sub RemoveWordReference()
    >> >>
    >> >> RemoveReference "Word"
    >> >>
    >> >> End Sub
    >> >>
    >> >> Sub RemoveReference(strReference As String)
    >> >>
    >> >> On Error GoTo ERROROUT
    >> >>
    >> >> Dim R As Object
    >> >>
    >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> If R.Name = strReference Then
    >> >> ThisWorkbook.VBProject.References.Remove R
    >> >> Exit Sub
    >> >> End If
    >> >> Next
    >> >>
    >> >> ERROROUT:
    >> >> On Error GoTo 0
    >> >>
    >> >> End Sub
    >> >>
    >> >> As it does Dim R As Object, rather than R as Reference you don't need
    >> >> a
    >> >> reference to
    >> >> Microsoft Visual Basic for Applications Extensibility.
    >> >>
    >> >>
    >> >> RBS
    >> >>
    >> >>
    >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    >> >> > Thanks a lot RB Smissaert !
    >> >> >
    >> >> > 1° - Does this procedure work if Word 2000 is installed as well with
    >> > Word
    >> >> > 2003 installed?
    >> >> > 2° - I need to remove the reference because another VBA procedure
    >> >> > (which
    >> >> > doesn't uses Word) hangs if the reference is still there!
    >> >> > Thanks again !
    >> >> >
    >> >> > Newbie
    >> >> >
    >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > message
    >> > de
    >> >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> >> >> Sub ActivateWordLibrary()
    >> >> >>
    >> >> >> Dim R
    >> >> >>
    >> >> >> On Error Resume Next
    >> >> >>
    >> >> >> 'no need to carry on if the Word Object Library is already
    >> >> >> there
    >> >> >>
    >> >> >> '---------------------------------------------------------------
    >> >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    >> >> >> Exit Sub
    >> >> >> End If
    >> >> >> Next
    >> >> >>
    >> >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> >> >> Major:=0, Minor:=0
    >> >> >>
    >> >> >> On Error GoTo 0
    >> >> >>
    >> >> >> End Sub
    >> >> >>
    >> >> >> You can remove the reference after, but not really neccessary.
    >> >> >>
    >> >> >> RBS
    >> >> >>
    >> >> >>
    >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> >> >> > Hello
    >> >> >> > As the Late Binding seems very slow, I would like to create a
    >> > reference
    >> >> > on
    >> >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000, some
    >> >> >> > others
    >> >> >> > are
    >> >> >> > running Office 2003.
    >> >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    > Microsoft
    >> >> > Word
    >> >> >> > 11.0 (2000 and 2003)?
    >> >> >> > How to remove this Reference after the VBA procedure has done its
    >> > work?
    >> >> >> > Thanks
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  15. #15
    Newbie
    Guest

    Re: Dynamic References to Word?

    Hi Bart,

    Really Wonderful !

    Newbie

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    news:eJz2U21WGHA.4148@TK2MSFTNGP03.phx.gbl...
    > This will all do it a bit better.
    > Will dump all the references of all open projects:
    >
    > Sub ListExcelReferences()
    >
    > 'to list all the references in Excel
    > '-----------------------------------
    >
    > Dim i As Long
    > Dim n As Long
    > Dim iRefCount As Long
    > Dim VBProj As Object
    >
    > Cells.Clear
    >
    > Cells(1).Value = "Project name"
    > Cells(2).Value = "Project file"
    > Cells(3).Value = "Reference Name"
    > Cells(4).Value = "Description"
    > Cells(5).Value = "FullPath"
    > Cells(6).Value = "GUID"
    > Cells(7).Value = "Major"
    > Cells(8).Value = "Minor"
    >
    > On Error Resume Next 'as an un-saved workbook has no filename

    yet
    >
    > For Each VBProj In Application.VBE.VBProjects
    > n = n + 1
    > With VBProj
    > iRefCount = .References.Count
    > With .References
    > For i = 1 To iRefCount
    > n = n + 1
    > If i = 1 Then
    > Cells(n, 1).Value = VBProj.Name
    > Cells(n, 2).Value = VBProj.Filename
    > If Err.Number = 76 Then 'Path not found
    > Cells(n, 2).Value = "Project not saved yet"
    > Err.Clear
    > End If
    > End If
    > Cells(n, 3).Value = .Item(i).Name
    > Cells(n, 4).Value = .Item(i).Description
    > Cells(n, 5).Value = .Item(i).FullPath
    > Cells(n, 6).Value = .Item(i).GUID
    > Cells(n, 7).Value = .Item(i).Major
    > Cells(n, 8).Value = .Item(i).Minor
    > Next i
    > End With
    > End With
    > Next
    >
    > On Error GoTo 0
    >
    > ThinRightBorder Range(Cells(2), Cells(n, 2))
    > Range(Cells(1), Cells(8)).Font.Bold = True
    > MediumBorder Range(Cells(1), Cells(8))
    > Range(Cells(1), Cells(n, 8)).Columns.AutoFit
    >
    > End Sub
    >
    > Sub MediumBorder(rng As Range, Optional wsh As Worksheet)
    >
    > 'puts a medium border around the passed range
    > '--------------------------------------------
    >
    > Dim Sh As Worksheet
    >
    > If wsh Is Nothing Then
    > Set Sh = ActiveWorkbook.ActiveSheet
    > Else
    > Set Sh = wsh
    > End If
    >
    > With Sh.Range(rng.Address)
    > .Borders(xlDiagonalDown).LineStyle = xlNone
    > .Borders(xlDiagonalUp).LineStyle = xlNone
    > With .Borders(xlEdgeLeft)
    > .LineStyle = xlContinuous
    > .Weight = xlMedium
    > .ColorIndex = xlAutomatic
    > End With
    > With .Borders(xlEdgeTop)
    > .LineStyle = xlContinuous
    > .Weight = xlMedium
    > .ColorIndex = xlAutomatic
    > End With
    > With .Borders(xlEdgeBottom)
    > .LineStyle = xlContinuous
    > .Weight = xlMedium
    > .ColorIndex = xlAutomatic
    > End With
    > With .Borders(xlEdgeRight)
    > .LineStyle = xlContinuous
    > .Weight = xlMedium
    > .ColorIndex = xlAutomatic
    > End With
    > End With
    >
    > End Sub
    >
    > Sub ThinRightBorder(rng As Range)
    >
    > With rng
    > With .Borders(xlEdgeRight)
    > .LineStyle = xlContinuous
    > .Weight = xlThin
    > .ColorIndex = xlAutomatic
    > End With
    > End With
    >
    > End Sub
    >
    >
    > RBS
    >
    >
    > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    > news:%23wJ6gS1WGHA.4652@TK2MSFTNGP04.phx.gbl...
    > > Just for interest, not sure what you gain with Byte, it is much shower
    > > than
    > > Long. I get it at about 16 times slower. (Integer is about 10 times
    > > slower). (and I don't think you are saving any space either - probably
    > > using the same amount of space although I haven't explored it).
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > >
    > > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > > news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    > >> The way to get those GUID's is like this:
    > >> First set the reference manually in the VBE under Tools, References.
    > >> Then run code like this:
    > >>
    > >>
    > >> Sub GetLibraryGUID()
    > >>
    > >> Dim c As Byte
    > >> Dim myCheck As Long
    > >> Dim P As Boolean
    > >> Dim rng As Range
    > >> Dim i As Byte
    > >>
    > >> c = ActiveWorkbook.VBProject.References.Count
    > >>
    > >> On Error Resume Next
    > >> Dim Message, Title, Default, T As Single
    > >> Message = "NUMBER ?" & Chr(13) & "________"
    > >> Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    > >> Default = c
    > >> T = InputBox(Message, Title, Default, 3500, 3500)
    > >>
    > >> If Not T Mod 1 = 0 Then
    > >> Exit Sub
    > >> End If
    > >>
    > >> If T < 1 Or T > c Then
    > >> Exit Sub
    > >> End If
    > >>
    > >> MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    > >> ActiveWorkbook.VBProject.References(T).Name & vbCrLf &

    vbCrLf
    > >> &

    > > _
    > >> "MAJOR : " & _
    > >> ActiveWorkbook.VBProject.References.Item(T).Major & _
    > >> vbCrLf & vbCrLf & "MINOR : " & _
    > >> ActiveWorkbook.VBProject.References.Item(T).Minor & _
    > >> vbCrLf & vbCrLf & _
    > >> "GUID ( " & T & " ) : " & _
    > >> ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    > >> " REFERENCES GUID : ITEM " & T
    > >>
    > >> myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    > >> vbYesNo, " GetLibraryGUID")
    > >>
    > >> If myCheck = vbNo Then
    > >> Exit Sub
    > >> End If
    > >>
    > >> If ActiveSheet.ProtectContents = True Then
    > >> P = True
    > >> ActiveSheet.Unprotect
    > >> Else
    > >> P = False
    > >> End If
    > >>
    > >> Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    > >> Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    > >>
    > >> For Each rng In Selection.Cells
    > >> If Not IsEmpty(rng) Then
    > >> i = i + 1
    > >> End If
    > >> Next
    > >>
    > >> If i > 0 Then
    > >> myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    > >> vbYesNo, " GetLibraryGUID")
    > >> If myCheck = vbNo Then
    > >> Exit Sub
    > >> End If
    > >> End If
    > >>
    > >> On Error Resume Next
    > >> ActiveCell.Value = "NAME :"
    > >> ActiveCell.Offset(1, 0).Value = "MAJOR :"
    > >> ActiveCell.Offset(2, 0).Value = "MINOR :"
    > >> ActiveCell.Offset(3, 0).Value = "GUID :"
    > >> ActiveCell.Offset(0, 1).Value = _
    > >> ActiveWorkbook.VBProject.References(T).Name
    > >> ActiveCell.Offset(1, 1).Value = _
    > >> ActiveWorkbook.VBProject.References.Item(T).Major
    > >> ActiveCell.Offset(2, 1).Value = _
    > >> ActiveWorkbook.VBProject.References.Item(T).Minor
    > >> ActiveCell.Offset(3, 1).Value = _
    > >> ActiveWorkbook.VBProject.References.Item(T).GUID
    > >>
    > >> If P = True Then
    > >> ActiveSheet.Protect
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> That will give you all the information you need.
    > >> Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem

    where
    > > you
    > >> specified a higher version than the one on the user's machine.
    > >>
    > >>
    > >> RBS
    > >>
    > >>
    > >> "Newbie" <newbie@libre.fr> wrote in message
    > >> news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    > >> > Thanks a lot RBS !
    > >> > Very useful!
    > >> > PS : Where can I found all the GUID such as
    > >> > "{00020905-0000-0000-C000-000000000046} ???
    > >> > Thanks again
    > >> >
    > >> >
    > >> >
    > >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    message
    > > de
    > >> > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    > >> >> Yes, should work with all versions of Word as it does Major:=0,

    > > Minor:=0.
    > >> >>
    > >> >>
    > >> >> To remove do this:
    > >> >>
    > >> >> Sub RemoveWordReference()
    > >> >>
    > >> >> RemoveReference "Word"
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> Sub RemoveReference(strReference As String)
    > >> >>
    > >> >> On Error GoTo ERROROUT
    > >> >>
    > >> >> Dim R As Object
    > >> >>
    > >> >> For Each R In ThisWorkbook.VBProject.References
    > >> >> If R.Name = strReference Then
    > >> >> ThisWorkbook.VBProject.References.Remove R
    > >> >> Exit Sub
    > >> >> End If
    > >> >> Next
    > >> >>
    > >> >> ERROROUT:
    > >> >> On Error GoTo 0
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> As it does Dim R As Object, rather than R as Reference you don't

    need
    > >> >> a
    > >> >> reference to
    > >> >> Microsoft Visual Basic for Applications Extensibility.
    > >> >>
    > >> >>
    > >> >> RBS
    > >> >>
    > >> >>
    > >> >> "Newbie" <newbie@libre.fr> wrote in message
    > >> >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    > >> >> > Thanks a lot RB Smissaert !
    > >> >> >
    > >> >> > 1° - Does this procedure work if Word 2000 is installed as well

    with
    > >> > Word
    > >> >> > 2003 installed?
    > >> >> > 2° - I need to remove the reference because another VBA procedure
    > >> >> > (which
    > >> >> > doesn't uses Word) hangs if the reference is still there!
    > >> >> > Thanks again !
    > >> >> >
    > >> >> > Newbie
    > >> >> >
    > >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > > message
    > >> > de
    > >> >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    > >> >> >> Sub ActivateWordLibrary()
    > >> >> >>
    > >> >> >> Dim R
    > >> >> >>
    > >> >> >> On Error Resume Next
    > >> >> >>
    > >> >> >> 'no need to carry on if the Word Object Library is already
    > >> >> >> there
    > >> >> >>
    > >> >> >> '---------------------------------------------------------------
    > >> >> >> For Each R In ThisWorkbook.VBProject.References
    > >> >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}" Then
    > >> >> >> Exit Sub
    > >> >> >> End If
    > >> >> >> Next
    > >> >> >>
    > >> >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    > >> >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    > >> >> >> Major:=0, Minor:=0
    > >> >> >>
    > >> >> >> On Error GoTo 0
    > >> >> >>
    > >> >> >> End Sub
    > >> >> >>
    > >> >> >> You can remove the reference after, but not really neccessary.
    > >> >> >>
    > >> >> >> RBS
    > >> >> >>
    > >> >> >>
    > >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    > >> >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    > >> >> >> > Hello
    > >> >> >> > As the Late Binding seems very slow, I would like to create a
    > >> > reference
    > >> >> > on
    > >> >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000,

    some
    > >> >> >> > others
    > >> >> >> > are
    > >> >> >> > running Office 2003.
    > >> >> >> > How can i create a Reference for Microsoft Word 9.0 or for

    > > Microsoft
    > >> >> > Word
    > >> >> >> > 11.0 (2000 and 2003)?
    > >> >> >> > How to remove this Reference after the VBA procedure has done

    its
    > >> > work?
    > >> >> >> > Thanks
    > >> >> >> >
    > >> >> >> >
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  16. #16
    RB Smissaert
    Guest

    Re: Dynamic References to Word?

    No trouble, I had the code already.
    I take it you got this all fixed then.

    RBS

    "Newbie" <newbie@libre.fr> wrote in message
    news:OLg3nf7WGHA.4452@TK2MSFTNGP04.phx.gbl...
    > Hi Bart,
    >
    > Really Wonderful !
    >
    > Newbie
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le message de
    > news:eJz2U21WGHA.4148@TK2MSFTNGP03.phx.gbl...
    >> This will all do it a bit better.
    >> Will dump all the references of all open projects:
    >>
    >> Sub ListExcelReferences()
    >>
    >> 'to list all the references in Excel
    >> '-----------------------------------
    >>
    >> Dim i As Long
    >> Dim n As Long
    >> Dim iRefCount As Long
    >> Dim VBProj As Object
    >>
    >> Cells.Clear
    >>
    >> Cells(1).Value = "Project name"
    >> Cells(2).Value = "Project file"
    >> Cells(3).Value = "Reference Name"
    >> Cells(4).Value = "Description"
    >> Cells(5).Value = "FullPath"
    >> Cells(6).Value = "GUID"
    >> Cells(7).Value = "Major"
    >> Cells(8).Value = "Minor"
    >>
    >> On Error Resume Next 'as an un-saved workbook has no filename

    > yet
    >>
    >> For Each VBProj In Application.VBE.VBProjects
    >> n = n + 1
    >> With VBProj
    >> iRefCount = .References.Count
    >> With .References
    >> For i = 1 To iRefCount
    >> n = n + 1
    >> If i = 1 Then
    >> Cells(n, 1).Value = VBProj.Name
    >> Cells(n, 2).Value = VBProj.Filename
    >> If Err.Number = 76 Then 'Path not found
    >> Cells(n, 2).Value = "Project not saved yet"
    >> Err.Clear
    >> End If
    >> End If
    >> Cells(n, 3).Value = .Item(i).Name
    >> Cells(n, 4).Value = .Item(i).Description
    >> Cells(n, 5).Value = .Item(i).FullPath
    >> Cells(n, 6).Value = .Item(i).GUID
    >> Cells(n, 7).Value = .Item(i).Major
    >> Cells(n, 8).Value = .Item(i).Minor
    >> Next i
    >> End With
    >> End With
    >> Next
    >>
    >> On Error GoTo 0
    >>
    >> ThinRightBorder Range(Cells(2), Cells(n, 2))
    >> Range(Cells(1), Cells(8)).Font.Bold = True
    >> MediumBorder Range(Cells(1), Cells(8))
    >> Range(Cells(1), Cells(n, 8)).Columns.AutoFit
    >>
    >> End Sub
    >>
    >> Sub MediumBorder(rng As Range, Optional wsh As Worksheet)
    >>
    >> 'puts a medium border around the passed range
    >> '--------------------------------------------
    >>
    >> Dim Sh As Worksheet
    >>
    >> If wsh Is Nothing Then
    >> Set Sh = ActiveWorkbook.ActiveSheet
    >> Else
    >> Set Sh = wsh
    >> End If
    >>
    >> With Sh.Range(rng.Address)
    >> .Borders(xlDiagonalDown).LineStyle = xlNone
    >> .Borders(xlDiagonalUp).LineStyle = xlNone
    >> With .Borders(xlEdgeLeft)
    >> .LineStyle = xlContinuous
    >> .Weight = xlMedium
    >> .ColorIndex = xlAutomatic
    >> End With
    >> With .Borders(xlEdgeTop)
    >> .LineStyle = xlContinuous
    >> .Weight = xlMedium
    >> .ColorIndex = xlAutomatic
    >> End With
    >> With .Borders(xlEdgeBottom)
    >> .LineStyle = xlContinuous
    >> .Weight = xlMedium
    >> .ColorIndex = xlAutomatic
    >> End With
    >> With .Borders(xlEdgeRight)
    >> .LineStyle = xlContinuous
    >> .Weight = xlMedium
    >> .ColorIndex = xlAutomatic
    >> End With
    >> End With
    >>
    >> End Sub
    >>
    >> Sub ThinRightBorder(rng As Range)
    >>
    >> With rng
    >> With .Borders(xlEdgeRight)
    >> .LineStyle = xlContinuous
    >> .Weight = xlThin
    >> .ColorIndex = xlAutomatic
    >> End With
    >> End With
    >>
    >> End Sub
    >>
    >>
    >> RBS
    >>
    >>
    >> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    >> news:%23wJ6gS1WGHA.4652@TK2MSFTNGP04.phx.gbl...
    >> > Just for interest, not sure what you gain with Byte, it is much shower
    >> > than
    >> > Long. I get it at about 16 times slower. (Integer is about 10 times
    >> > slower). (and I don't think you are saving any space either - probably
    >> > using the same amount of space although I haven't explored it).
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> >
    >> >
    >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> > news:%23W%23nA60WGHA.4144@TK2MSFTNGP04.phx.gbl...
    >> >> The way to get those GUID's is like this:
    >> >> First set the reference manually in the VBE under Tools, References.
    >> >> Then run code like this:
    >> >>
    >> >>
    >> >> Sub GetLibraryGUID()
    >> >>
    >> >> Dim c As Byte
    >> >> Dim myCheck As Long
    >> >> Dim P As Boolean
    >> >> Dim rng As Range
    >> >> Dim i As Byte
    >> >>
    >> >> c = ActiveWorkbook.VBProject.References.Count
    >> >>
    >> >> On Error Resume Next
    >> >> Dim Message, Title, Default, T As Single
    >> >> Message = "NUMBER ?" & Chr(13) & "________"
    >> >> Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
    >> >> Default = c
    >> >> T = InputBox(Message, Title, Default, 3500, 3500)
    >> >>
    >> >> If Not T Mod 1 = 0 Then
    >> >> Exit Sub
    >> >> End If
    >> >>
    >> >> If T < 1 Or T > c Then
    >> >> Exit Sub
    >> >> End If
    >> >>
    >> >> MsgBox "REFERENCE ( " & T & " ) NAME : " & _
    >> >> ActiveWorkbook.VBProject.References(T).Name & vbCrLf &

    > vbCrLf
    >> >> &
    >> > _
    >> >> "MAJOR : " & _
    >> >> ActiveWorkbook.VBProject.References.Item(T).Major & _
    >> >> vbCrLf & vbCrLf & "MINOR : " & _
    >> >> ActiveWorkbook.VBProject.References.Item(T).Minor & _
    >> >> vbCrLf & vbCrLf & _
    >> >> "GUID ( " & T & " ) : " & _
    >> >> ActiveWorkbook.VBProject.References.Item(T).GUID, , _
    >> >> " REFERENCES GUID : ITEM " & T
    >> >>
    >> >> myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
    >> >> vbYesNo, " GetLibraryGUID")
    >> >>
    >> >> If myCheck = vbNo Then
    >> >> Exit Sub
    >> >> End If
    >> >>
    >> >> If ActiveSheet.ProtectContents = True Then
    >> >> P = True
    >> >> ActiveSheet.Unprotect
    >> >> Else
    >> >> P = False
    >> >> End If
    >> >>
    >> >> Range(Cells(ActiveCell.Row, ActiveCell.Column), _
    >> >> Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select
    >> >>
    >> >> For Each rng In Selection.Cells
    >> >> If Not IsEmpty(rng) Then
    >> >> i = i + 1
    >> >> End If
    >> >> Next
    >> >>
    >> >> If i > 0 Then
    >> >> myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
    >> >> vbYesNo, " GetLibraryGUID")
    >> >> If myCheck = vbNo Then
    >> >> Exit Sub
    >> >> End If
    >> >> End If
    >> >>
    >> >> On Error Resume Next
    >> >> ActiveCell.Value = "NAME :"
    >> >> ActiveCell.Offset(1, 0).Value = "MAJOR :"
    >> >> ActiveCell.Offset(2, 0).Value = "MINOR :"
    >> >> ActiveCell.Offset(3, 0).Value = "GUID :"
    >> >> ActiveCell.Offset(0, 1).Value = _
    >> >> ActiveWorkbook.VBProject.References(T).Name
    >> >> ActiveCell.Offset(1, 1).Value = _
    >> >> ActiveWorkbook.VBProject.References.Item(T).Major
    >> >> ActiveCell.Offset(2, 1).Value = _
    >> >> ActiveWorkbook.VBProject.References.Item(T).Minor
    >> >> ActiveCell.Offset(3, 1).Value = _
    >> >> ActiveWorkbook.VBProject.References.Item(T).GUID
    >> >>
    >> >> If P = True Then
    >> >> ActiveSheet.Protect
    >> >> End If
    >> >>
    >> >> End Sub
    >> >>
    >> >> That will give you all the information you need.
    >> >> Keep in mind that you can do Major:=0, Minor:=0 to avoid a problem

    > where
    >> > you
    >> >> specified a higher version than the one on the user's machine.
    >> >>
    >> >>
    >> >> RBS
    >> >>
    >> >>
    >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> news:OIVABc0WGHA.1220@TK2MSFTNGP02.phx.gbl...
    >> >> > Thanks a lot RBS !
    >> >> > Very useful!
    >> >> > PS : Where can I found all the GUID such as
    >> >> > "{00020905-0000-0000-C000-000000000046} ???
    >> >> > Thanks again
    >> >> >
    >> >> >
    >> >> >
    >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le

    > message
    >> > de
    >> >> > news:%23f1hfeyWGHA.2268@TK2MSFTNGP02.phx.gbl...
    >> >> >> Yes, should work with all versions of Word as it does Major:=0,
    >> > Minor:=0.
    >> >> >>
    >> >> >>
    >> >> >> To remove do this:
    >> >> >>
    >> >> >> Sub RemoveWordReference()
    >> >> >>
    >> >> >> RemoveReference "Word"
    >> >> >>
    >> >> >> End Sub
    >> >> >>
    >> >> >> Sub RemoveReference(strReference As String)
    >> >> >>
    >> >> >> On Error GoTo ERROROUT
    >> >> >>
    >> >> >> Dim R As Object
    >> >> >>
    >> >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> >> If R.Name = strReference Then
    >> >> >> ThisWorkbook.VBProject.References.Remove R
    >> >> >> Exit Sub
    >> >> >> End If
    >> >> >> Next
    >> >> >>
    >> >> >> ERROROUT:
    >> >> >> On Error GoTo 0
    >> >> >>
    >> >> >> End Sub
    >> >> >>
    >> >> >> As it does Dim R As Object, rather than R as Reference you don't

    > need
    >> >> >> a
    >> >> >> reference to
    >> >> >> Microsoft Visual Basic for Applications Extensibility.
    >> >> >>
    >> >> >>
    >> >> >> RBS
    >> >> >>
    >> >> >>
    >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> >> news:eAZ$jKyWGHA.3800@TK2MSFTNGP03.phx.gbl...
    >> >> >> > Thanks a lot RB Smissaert !
    >> >> >> >
    >> >> >> > 1° - Does this procedure work if Word 2000 is installed as well

    > with
    >> >> > Word
    >> >> >> > 2003 installed?
    >> >> >> > 2° - I need to remove the reference because another VBA procedure
    >> >> >> > (which
    >> >> >> > doesn't uses Word) hangs if the reference is still there!
    >> >> >> > Thanks again !
    >> >> >> >
    >> >> >> > Newbie
    >> >> >> >
    >> >> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> a écrit dans le
    >> > message
    >> >> > de
    >> >> >> > news:e210wqxWGHA.4484@TK2MSFTNGP02.phx.gbl...
    >> >> >> >> Sub ActivateWordLibrary()
    >> >> >> >>
    >> >> >> >> Dim R
    >> >> >> >>
    >> >> >> >> On Error Resume Next
    >> >> >> >>
    >> >> >> >> 'no need to carry on if the Word Object Library is already
    >> >> >> >> there
    >> >> >> >>
    >> >> >> >> '---------------------------------------------------------------
    >> >> >> >> For Each R In ThisWorkbook.VBProject.References
    >> >> >> >> If R.GUID = "{00020905-0000-0000-C000-000000000046}"
    >> >> >> >> Then
    >> >> >> >> Exit Sub
    >> >> >> >> End If
    >> >> >> >> Next
    >> >> >> >>
    >> >> >> >> ThisWorkbook.VBProject.References.AddFromGuid _
    >> >> >> >> GUID:="{00020905-0000-0000-C000-000000000046}", _
    >> >> >> >> Major:=0, Minor:=0
    >> >> >> >>
    >> >> >> >> On Error GoTo 0
    >> >> >> >>
    >> >> >> >> End Sub
    >> >> >> >>
    >> >> >> >> You can remove the reference after, but not really neccessary.
    >> >> >> >>
    >> >> >> >> RBS
    >> >> >> >>
    >> >> >> >>
    >> >> >> >> "Newbie" <newbie@libre.fr> wrote in message
    >> >> >> >> news:OUgUyvwWGHA.5076@TK2MSFTNGP04.phx.gbl...
    >> >> >> >> > Hello
    >> >> >> >> > As the Late Binding seems very slow, I would like to create a
    >> >> > reference
    >> >> >> > on
    >> >> >> >> > Word 2000 or Word 2003 (Several PCs are running Office 2000,

    > some
    >> >> >> >> > others
    >> >> >> >> > are
    >> >> >> >> > running Office 2003.
    >> >> >> >> > How can i create a Reference for Microsoft Word 9.0 or for
    >> > Microsoft
    >> >> >> > Word
    >> >> >> >> > 11.0 (2000 and 2003)?
    >> >> >> >> > How to remove this Reference after the VBA procedure has done

    > its
    >> >> > work?
    >> >> >> >> > Thanks
    >> >> >> >> >
    >> >> >> >> >
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1