Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
References changing between different platforms - DLL hell returns!
The problem:
o Excel VBA workbook developed on Windows 2000 using/referencing Microsoft ADO Ext. 2.7 for DLL and Security library o Load and save workbook on Windows XP system o Re-open the workbook on Windows 2000 system and VBA won't run as it's missing the reference to Microsoft ADO Ext. 2.8 for DLL and Security library What's happening is that when it's opened on the Windows XP system, Excel is automatically upgrading the reference to v2.8 of the library. This is then saved in the workbook meaning it can't be opened/used with an earlier version of the library. Actually, Windows 2000/XP isn't the issue - it would happen on two Windows 2000 systems if they had different versions of the library. The same doesn't happen with plain ADO - we reference v2.5 and this stays as v2.5 even when used on Windpws XP running a later version of ADO. Interesting, the file name for ADOX v2.7 and v2.8 is the same: c:\program files\common files\shared\ado\msadox.dll Is there a fix?? Thanks, Rob. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
References changing between different platforms - DLL hell returns!
Is there a fix??
Later... Okay, the information about the original reference under ADOX 2.7 is as follows: Reference {00000600-0000-0010-8000-00AA006D2EA4} Major 2 Minor 7 Broken False Full path C:\Program Files\Common Files\System\ADO\msadox.dll Name ADOX Description Microsoft ADO Ext. 2.7 for DDL and Security But when the workbook has been opened under ADOX 2.8, the reference is changed to exactly the same as above except the minor version is 8 as opposed to 7, i.e. 2.8 versus 2.7. The GUID is the same for both versions. So I thought - simple, at program startup walk through the references looking for the broken reference, remove the reference and add back in the original. But the following code doesn't work - well it works all the way until we try and remove the broken reference when Excel throws an error: ----- Run-time error '-2147319779 (8002801d' Object library not registered. ---- Of course it's not registered!! That's why I'm trying to remove it. So the question now is "HOW DO YOU REMOVE A MISSING REFERENCE IN VBA". Cheers, Rob. Private Const ADOX_2_7_GUID = "{00000600-0000-0010-8000-00AA006D2EA4}" Sub RebindADOX() Dim TargetWorkbook As Workbook Set TargetWorkbook = Workbooks("Lung Cancer Trial Tracker.xls") Dim TargetProject As VBIDE.VBProject Set TargetProject = TargetWorkbook.VBProject Dim VBReference As VBIDE.Reference For Each VBReference In TargetProject.References If VBReference.IsBroken And VBReference.GUID = ADOX_2_7_GUID Then TargetProject.References.Remove VBReference *** FAILS HERE **** TargetProject.References.AddFromGuid ADOX_2_7_GUID, 2, 7 End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
References changing between different platforms - DLL hell returns!
question now is "HOW DO YOU REMOVE A MISSING REFERENCE IN VBA".
I've read some more on Google and I think I'm onto a non-starter here - the question has been asked before and the answer that I've found so far is "You can't remove a missing reference from VBA, the functionality doesn't exist". The answer is to use late binding which isn't half-way as nice but at least it'll work. Now what's the CreateObject syntax for ADOX catalog please? :-) Thanks, Rob. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
References changing between different platforms - DLL hell returns!
Hi
Here is some code snippet for your reference. Dim cat As Object Dim tbl As Object Dim col As Object Set cat = CreateObject("ADOX.Catalog") cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "E:\test\excel\test2.xls;Extended Properties=Excel 8.0" Set tbl = CreateObject("ADOX.Table") tbl.Name = "TestTable" Set col = CreateObject("ADOX.Column") With col .Name = "Col1" .Type = adDouble End With tbl.Columns.Append col Set col = Nothing Set col = CreateObject("ADOX.Column") With col .Name = "Col2" .Type = adVarWChar End With tbl.Columns.Append col cat.Tables.Append tbl Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing worksheet references | Excel Worksheet Functions | |||
Excel platforms | Excel Discussion (Misc queries) | |||
Need a count of #of times a result occurred on multiple platforms | Excel Worksheet Functions | |||
Changing many references at a time. | Excel Discussion (Misc queries) | |||
Changing cell references | Excel Discussion (Misc queries) |