Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
Hello,
How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
Hi,
Interested to see if anyone knows this, I suspect that you can't. I think maybe what you want to achieve you would do through 'late binding' i.e. Dim oWord As Object Set oWord = CreateObject("Word.Application") That way whatever version of word you have you get an instance of it. The coding of what you do with word then becomes more difficult as you lack the intellisense feature. Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
for word it would be:
Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
Thanks a lot Tom
Gérard "Tom Ogilvy" a écrit dans le message de ... for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
Tom,
It works fine! but i have 2 other questions : - Is the syntaxe the same for Microsoft Word 9.0 and for Microsoft Word 11.0 (2000 and 2003)? - How to remove this Reference ? Thanks Gérard "Tom Ogilvy" a écrit dans le message de ... for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
I just got really excited here... When trying to send spreadsheets through
different versions of Lotus Notes or working with different versions of our 3270 emulator I run into problems with compile errors because some of the users' machines do not have the references they need. With that in mind I have two questions: 1) How can I identify the global unique ID value of the reference objects? 2) Could I have some code that would test each machine for different reference IDs and apply the proper one? (if so, a small sample would be appreciated) Thanks for the epiphany, Tom!!! patrick "Tom Ogilvy" wrote: for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
The easiest way is to set the reference in Excel and then query the
reference for its GUID value. This is some code posted recently by RB Smisseart that list data on the references A GUID should be unique and consistent for an application across versions. The registry will have one version of the application mapped to application. So if you have multiple versions, it is usually mapped to the last installed. You can look in the registry to find these entries (but for a small population, setting them in Excel and then reading them is probably easiest). ---- code from Bart ------- 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 -- Regards, Tom Ogilvy "Patrick" wrote in message ... I just got really excited here... When trying to send spreadsheets through different versions of Lotus Notes or working with different versions of our 3270 emulator I run into problems with compile errors because some of the users' machines do not have the references they need. With that in mind I have two questions: 1) How can I identify the global unique ID value of the reference objects? 2) Could I have some code that would test each machine for different reference IDs and apply the proper one? (if so, a small sample would be appreciated) Thanks for the epiphany, Tom!!! patrick "Tom Ogilvy" wrote: for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
Posting under the name Newbie? Assume you received your answer from Bart.
-- Regards, Tom Ogilvy "Gérard Ducouret" wrote in message ... Tom, It works fine! but i have 2 other questions : - Is the syntaxe the same for Microsoft Word 9.0 and for Microsoft Word 11.0 (2000 and 2003)? - How to remove this Reference ? Thanks Gérard "Tom Ogilvy" a écrit dans le message de ... for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a Reference with VBA
When you are assigning the reference do you have to reference the Major and
Minor?? Reflections for IBM version 8: {ECF246D9-E871-11D2-8CC2-00C04F72C0ED},8,0 Reflections for IBM version 11: {ECF246D9-E871-11D2-8CC2-00C04F72C0ED},11,0 If I send a sheet with VBA without referencing the Reflections for IBM Session... will I have to test the GUID major and minor to see which they have before assigning it... or could I just add the reference with the GUID itself? "Tom Ogilvy" wrote: Posting under the name Newbie? Assume you received your answer from Bart. -- Regards, Tom Ogilvy "Gérard Ducouret" wrote in message ... Tom, It works fine! but i have 2 other questions : - Is the syntaxe the same for Microsoft Word 9.0 and for Microsoft Word 11.0 (2000 and 2003)? - How to remove this Reference ? Thanks Gérard "Tom Ogilvy" a écrit dans le message de ... for word it would be: Dim s as String s = "{00020905-0000-0000-C000-000000000046}" Thisworkbook.VBProject.References.AddFromGuid s,0,0 -- Regards, Tom Ogilvy "Gérard Ducouret" wrote: Hello, How can I create a Reference (Such as Tools / Reference in a VBA module) to Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove the reference at the end of the procedure? Thanks ahead! Gérard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to take text from other cells to create a reference to a name | Excel Discussion (Misc queries) | |||
Can I create a 3-D reference from multiple workbooks | Excel Worksheet Functions | |||
How do I create a unique spreadsheet reference? | Excel Worksheet Functions | |||
How do I create a cell reference to return name of the tab? | Excel Discussion (Misc queries) | |||
Create a reference | Excel Programming |