Solved

Unreferencing an OCX in XL2000

Posted on 2002-04-30
7
506 Views
Last Modified: 2012-05-04
I have a template in Excel that is apparently referencing an ocx (sqaote32.ocx-Rational ActiveX Test).  I use the Package and Deployment wizard to create an installation for the template.  The Setup.LST file that is generated does not refer to the component that is giving me problems.  However, when I try and run the install I get the following error: "Component 'sqaote32.ocx' or one of it's dependencies not correctly registered: a file is missing or invalid."

I have checked all the obvious locations in the VBA code and cannot find any references to the component.

Pleae Help.

Samir
0
Comment
Question by:samird
  • 4
  • 3
7 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 500 total points
ID: 6981417
Hi Samir,

you said > checked all the obvious locations in the VBA

this means also for all components in the workbook?, it could be that one the add-inn is referenceing to the ocx you can look them up in the tools | references menu

it could be another ocx/dll referencing the ocx that gives the error maybe that is included in your project but still references to the old one

if nothing else will do i would create a clean workbook
-move all the components to it
-move all sheets to it, be sure to change the links if you would go this way

btw what would happen if you just included the ocx in your project? version conflicts or not if it doesn't hurt you could try that instead of creating a copy

i'm almost sure it's in one of the included elements and they're referencing one or the other way still to this one, but if you can't find it i would create a copy instead of looking a day or more for some reference

concluding
-look into the othere elements (addins,ocx,dll,xla)
-or create a clean copy without any history
-or include the ocx

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6982748
if above doesn't help or you want to try something else

you can try to remove the reference programmatically, with

  With ThisWorkbook.VBProject
    .References.Remove .References("MSACAL")
  End With

you can also check the presence of the reference, you could use code like the following

  On Error Resume Next
  Set ref = Application.ThisWorkbook.VBProject.References("MSACAL")
  On Error GoTo Error_ReferenceFromFile
  If ref Is Nothing Then
    msgbox "No reference found"
  End If

this is only to solve your problem the code can be taken out when it is solved

HTH:O)Bruintje
0
 

Author Comment

by:samird
ID: 6983463
I added the code that you suggested.  I have a couple of questions.  What is "MSACAL"?  Is this a reference to a component that your code is looking for?  I get a "Subscript out of range" error using when I specify "MSACAL".  If I don't specifiy anything and perform a watch on "ref" it shows me the default VBA and office references.  I can't see the reference that I think is causing me problems.

samir
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 44

Expert Comment

by:bruintje
ID: 6983560
sorry "MSACAL" was just the sample, it should be "sqaote32"
0
 

Author Comment

by:samird
ID: 6984029
I substituted "sqaote32" instead but I still get the "Subscript out of range" error.  I tried using "FM20" (fm20.dll) which is the MS Forms dll and is also selected as a reference in the reference list, and I get the same error.  Any clues?

samir
0
 

Author Comment

by:samird
ID: 6988924
Brian,

Thanks for your help.  I thought I would go ahead and give you the points.  I called Microsoft and after a 3 hour call with them, I ultimately started over as you recommended.  They could not figure out any way to determine what hidden references may lurking around in my spreadsheet.  I had to tell them about your code snipet.  Good thing I had a support incident left.  Anyway, thanks for the help.

Samir
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6991610
Samir, sorry to hear you had to start over, you had to tell them about that snippet? that's funny think they would come up with some of their own

Good luck
Brian
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question