Missing VBA Reference

Posted by Dan on January 03, 2002 9:14 AM

I have a workbook with a recorded macro (that was slightly modified) that copies and pastes a new worksheet. It works fine, except on a couple of other peoples' computers that gets a 1004 runtime error: "Copy method of Worksheet class failed". When I check their VBA References, it shows "MISSING: Microsoft Office 8.0 Object Library"

I also noticed that these people are unable to record a new macro, even on a new workbook, getting the message "Unable to record".

I checked MS's web site, and did what it said (uncheck the missing references, ran a macro "fix" to remove references) but I still have the problem. Any ideas?

Here's the macro:
Sub AddTimeCardSheet()
'
'
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheet ("Template") 'this is where the error occurs
ActiveSheet.Unprotect
Range("F2:L3,S2,B6,B10:O11,D14:J14,D16:J16,O14,O16").Select
Selection.Locked = False
'Unlocks entry fields
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Protects worksheet
Range("B6").Select
End Sub

Posted by Dank on January 03, 2002 9:24 AM

Am I right in saying you're using Excel 97? Try searching the hard drive for a file named mso8.dll (it's called mso9.dll in Excel 2000 so I'm assuming the previous version will be called that). If you find it, click Tools, References and browse to the location of the file. Unless your users are using a pre-97 version of Excel then the Office object library should be present as it's installed as standard when any office application is installed.

Good luck,
Daniel.

Posted by Dan on January 03, 2002 9:48 AM

Sorry, yes I'm using XL97. On my computer, the file is named MSO97.dll. That is also the file name that the MS website references. I used the Browse button on her machine and pointed to that file, but it still says that it is missing. What gives? I even copied my .dll file to her machine and had the same results.

Posted by Dank on January 03, 2002 10:39 AM

Right, firstly make sure you've done this:-

Where it says Missing unclick the missing reference and click OK. Now go back into references. The Missing bit should have gone. Now browse to the DLL again. Click OK, save, close and reopen. Apologies if you've done this but I'm just exploring different options.

Now, if that doesn't work you're probably best off reinstalling Excel.

Dan.

Posted by Dan on January 03, 2002 11:33 AM

I'll give that a shot. Not sure if I did it exactly like that the first time. Otherwise we'll get it reinstalled. Thanks for your help.

Dan

Posted by Dan on January 03, 2002 1:08 PM

Thanks Daniel, that worked.