Solved

Unreferencing an OCX in XL2000

Posted on 2002-04-30
7
507 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

820 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