Re: Dynamic References to Word?

Hi Bart,

Really Wonderful !

Newbie

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> a écrit dans le message de
news:eJz2U21WGHA.4148@xxxxxxxxxxxxxxxxxxxxxxx
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 Work***)

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

Dim Sh As Work***

If wsh Is Nothing Then
Set Sh = ActiveWorkbook.Active***
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@xxxxxxx> wrote in message
news:%23wJ6gS1WGHA.4652@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> wrote in message
news:%23W%23nA60WGHA.4144@xxxxxxxxxxxxxxxxxxxxxxx
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 *** ?", _
vbYesNo, " GetLibraryGUID")

If myCheck = vbNo Then
Exit Sub
End If

If Active***.ProtectContents = True Then
P = True
Active***.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
Active***.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@xxxxxxxx> wrote in message
news:OIVABc0WGHA.1220@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> a écrit dans le
message
de
news:%23f1hfeyWGHA.2268@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxx> wrote in message
news:eAZ$jKyWGHA.3800@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> a écrit dans le
message
de
news:e210wqxWGHA.4484@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxx> wrote in message
news:OUgUyvwWGHA.5076@xxxxxxxxxxxxxxxxxxxxxxx
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














.

Loading