Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Hello --
Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Major and Minor represent the portions of the version.
Add a reference to "Microsoft Visual Basic for Applications Extensibility" library. Then open the object browser. Choose VBIDE from the dropdown and take a look at the Reference object. From help on Major Property: Returns a Long containing the major version number of the referenced type library. -- Rob van Gelder - http://www.vangelder.co.nz/excel "L Mehl" wrote in message ... Hello -- Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Rob --
Thank you. Do I place code at the app's start which queries the user's system for version numbers of the required DLLs and notifies the user if newer versions need to be loaded?? If so, do you know if example code for such a process is available on the web? My code thus far derives the following for one of the reference files used: for stdole2.tlb #Major = 2 #Minor =0 do these relate to properties of the file: File version is 2.10.3027.1 Product Version is 2.1 ? Thanks for any help. Larry Mehl "Rob van Gelder" wrote in message ... Major and Minor represent the portions of the version. Add a reference to "Microsoft Visual Basic for Applications Extensibility" library. Then open the object browser. Choose VBIDE from the dropdown and take a look at the Reference object. From help on Major Property: Returns a Long containing the major version number of the referenced type library. -- Rob van Gelder - http://www.vangelder.co.nz/excel "L Mehl" wrote in message ... Hello -- Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Do I place code at the app's start which queries the user's system for
version numbers of the required DLLs and notifies the user if newer versions need to be loaded?? You could do I guess. I don't do it, but maybe I should? I'll admit I don't know much about version compatibility in this area. More than likely you want to run your workbook on various PCs. Just be aware that checking References is considered "Suspicious Activity" by security features of later versions of Excel. -- Rob van Gelder - http://www.vangelder.co.nz/excel "L Mehl" wrote in message ... Rob -- Thank you. Do I place code at the app's start which queries the user's system for version numbers of the required DLLs and notifies the user if newer versions need to be loaded?? If so, do you know if example code for such a process is available on the web? My code thus far derives the following for one of the reference files used: for stdole2.tlb #Major = 2 #Minor =0 do these relate to properties of the file: File version is 2.10.3027.1 Product Version is 2.1 ? Thanks for any help. Larry Mehl "Rob van Gelder" wrote in message ... Major and Minor represent the portions of the version. Add a reference to "Microsoft Visual Basic for Applications Extensibility" library. Then open the object browser. Choose VBIDE from the dropdown and take a look at the Reference object. From help on Major Property: Returns a Long containing the major version number of the referenced type library. -- Rob van Gelder - http://www.vangelder.co.nz/excel "L Mehl" wrote in message ... Hello -- Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
You need to be aware that this code does not tell you what versions of an
application that is installed on a machine, but just what is referenced. For example, on my desktop machine I have XL97, Xl2000 and XL XP installed, but the code shows me the Excel version that I am running from not all 3. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rob van Gelder" wrote in message ... Do I place code at the app's start which queries the user's system for version numbers of the required DLLs and notifies the user if newer versions need to be loaded?? You could do I guess. I don't do it, but maybe I should? I'll admit I don't know much about version compatibility in this area. More than likely you want to run your workbook on various PCs. Just be aware that checking References is considered "Suspicious Activity" by security features of later versions of Excel. -- Rob van Gelder - http://www.vangelder.co.nz/excel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Thanks Rob and Bob --
This is DLL hell, isn't it. What is the smart way to set up an app so it can be distributed to users having various levels of Service Packs, versions of Excel, etc., so that the app will be likely to run in those different environments? The users of this app will most likely be able to understand the need to update to a specific service pack, etc., if I am able to tell them what to do. I need to be able to tell them what to do. Is one answer to "wrap" this Excel app in a dotNet Windows Forms application? Thanks for any further ideas. Larry "L Mehl" wrote in message ... Hello -- Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tools | References - information about references
Larry,
It sure is. Dot.Net is a no-no, as Office apps don't fully support, 2003 was the first to offer any sort of support. There is only one sure fire way, and that is to develop your code using the lowest version of Excel/VBA that is likely to be out in the field, sacrificing some of the newer functionality. Backward compatibility exists, forward never does. If you want to use a newer version and your audience is amenable to upgrading, send out installation notes that explain this explicitly, and rely on them to do so before installing your app. Sometimes the manual method works better<g. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "L Mehl" wrote in message ... Thanks Rob and Bob -- This is DLL hell, isn't it. What is the smart way to set up an app so it can be distributed to users having various levels of Service Packs, versions of Excel, etc., so that the app will be likely to run in those different environments? The users of this app will most likely be able to understand the need to update to a specific service pack, etc., if I am able to tell them what to do. I need to be able to tell them what to do. Is one answer to "wrap" this Excel app in a dotNet Windows Forms application? Thanks for any further ideas. Larry "L Mehl" wrote in message ... Hello -- Some code was posted here to list all current references to a worksheet. Can someone tell me what .Major and .Minor mean? Can I get other information about a reference, such as Version? What part of the Object Browser contains this information? Thanks for any help. L Mehl '------------------------------------ ' Description, Name, GUID, #MAjor, #Minor, full path. Dim refReference Dim i As Integer, x As Integer Dim iWorksheets As Integer, Y As Integer Dim strResultsTableName As String strResultsTableName = "Active VBE References" 'Count number of worksheets in workbook iWorksheets = ActiveWorkbook.Sheets.Count 'Check for duplicate Worksheet name i = ActiveWorkbook.Sheets.Count For x = 1 To i If Windows.Count = 0 Then Exit Sub If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then Worksheets(x).Activate If Err.Number = 9 Then Exit For End If Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'Exit Sub End If Next 'Add new worksheet at end of workbook ' where results will be located Worksheets.add.Move after:=Worksheets(Worksheets.Count) 'Name the new worksheet and set up Titles ActiveWorkbook.ActiveSheet.Name = strResultsTableName ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description" ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name" ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID" ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major" ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor" ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path" ActiveCell.Offset(1, 0).Select For Each refReference In Application.VBE.ActiveVBProject.References With ActiveCell .Value = refReference.Description .Offset(0, 1).Value = refReference.Name .Offset(0, 2).Value = refReference.GUID .Offset(0, 3).Value = refReference.Major .Offset(0, 4).Value = refReference.Minor .Offset(0, 5).Value = refReference.FullPath .Offset(1, 0).Select End With Next ' ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equivalent option for Tools-References in Excel 2007 | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Tools>References greyed out | Excel Discussion (Misc queries) | |||
Problem with VBE Menu -- Tools References | Excel Programming |