Excel 97 Macro Error

When I try to open a sheet with a macro in it I keep getting the following error:

Run-time error '32809':

Application-defined or object-defined error


This doesn't happen on other computers using the same system so I'm guessing maybe I don't have the right components installed. I only did a typical install. I took a look at the options for a custom install but I don't know what else I should install.
lhuttonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amauCommented:
Consider installing all components for the Visual Basic support in Excel 97. This should help since I had a similar problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lhuttonAuthor Commented:
I tried doing that, but I don't know exactly where to look for which components to install.
0
antratCommented:
Where does it take you when you "Debug"?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lhuttonAuthor Commented:
When I go to Debug, it opens the code for Module1 and highlights the following:

Private Sub ReInput()
    ' Move the cursor back to the cell for inputting file name
    Sheets("Parser").Select
    Range(InputCell).Select
End Sub
0
antratCommented:
Private sub indicates that the Sub procedure is accessible only to other procedures in the module where it is declared.

What is this sub for?

Do you have a sheet named "Parser"?

What other procedures are in module1?

What is the name of the sheet this happens on?

Are there any private subs in "ThisWorbook" or any of the Sheets?

Are there any Auto_Open ,Workbook_Open, Sheet_Change macros?


Sorry about the 20 questions.

When you de-bug hold your mouse pointer over the text "Sheets" and "InputCell" while it is highlighted and tell me what it sais. Are you sure the error # is '32809'

antrat

0
lhuttonAuthor Commented:
Importantly, please note that this sheet and its macro works fine on another machine running the same version of Excel.

Also, I didn't program the macro and have little working knowledge of macros :-)

I'm guessing this sub would be for moving the cursor back to the filename input cell after a file has been parsed.

There is one sheet in the book, Parser.

This is the full content of Module1:
------------------------------------------------------------------------
Const InputCell As String = "B4"

Sub Run()
    Dim InFileName As String
    Dim InFileNum As Integer
    Dim CurrentRow As Integer
    Dim CurrentColumn As Integer
    Dim AllCapital As Boolean
    Dim TempLine As String
    Dim TempVar As String
    Dim StartPt As Integer
    Dim EndPt As Integer
   
    ' Check if the filename has been entered
    InFileName = Worksheets("Parser").Range(InputCell).Text
    If (InFileName = "") Then
        MsgBox "Please input the name of the data file before pressing the Run button.", vbOKOnly, "Warning"
        ReInput
        Exit Sub
    End If
   
    ' Check if the file exists and can be opened
    InFileNum = FreeFile
    On Error GoTo InFileOpenErr
    Open InFileName For Input Access Read Lock Read Write As InFileNum
    On Error GoTo 0

    ' Clear the last result
    Worksheets("Result").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select

    ' Read the file and fill the data to the Result sheet
    CurrentRow = 1
    CurrentColumn = 0
    Do While Not (EOF(InFileNum))
        Line Input #InFileNum, TempLine
        StartPt = 1
        Do While (StartPt <= Len(TempLine))
            EndPt = InStr(StartPt, TempLine, " ")
                       
            If (StartPt = EndPt) Then                                   ' Current point is a space
                StartPt = StartPt + 1                                   ' Move to next position
            Else                                                        ' No more space
                If (EndPt = 0) Then
                TempVar = Mid(TempLine, StartPt, Len(TempLine) - StartPt + 1)
                    StartPt = Len(TempLine) + 1
                Else
                    TempVar = Mid(TempLine, StartPt, EndPt - StartPt)
                    StartPt = EndPt + 1
                End If

                If (CurrentColumn = 0) Then
                    CurrentColumn = CurrentColumn + 1
                    Worksheets("Result").Cells(CurrentRow, CurrentColumn) = TempVar
                Else
                    If (CheckAllCapital(TempVar) <> AllCapital) Then    ' A new variable
                        CurrentColumn = CurrentColumn + 1
                        Worksheets("Result").Cells(CurrentRow, CurrentColumn) = TempVar
                    Else
                        Worksheets("Result").Cells(CurrentRow, CurrentColumn) = _
                        Worksheets("Result").Cells(CurrentRow, CurrentColumn) & " " & TempVar
                    End If
                End If
                AllCapital = CheckAllCapital(TempVar)
            End If
        Loop
        CurrentRow = CurrentRow + 1
        CurrentColumn = 0
    Loop

    ' Clear the filename input and show the result page
    Worksheets("Parser").Range(InputCell) = ""
    Worksheets("Result").Select

    ' Close the file and exit
    Close InFileNum
    Exit Sub

InFileOpenErr:
    MsgBox "The input file " & Chr(34) & InFileName & Chr(34) & " cannot be opened. Please make sure the file exists and the full path is provided.", _
           vbOKOnly, "File open error"
    ReInput
    Exit Sub
End Sub

Private Sub ReInput()
    ' Move the cursor back to the cell for inputting file name
    Sheets("Parser").Select
    Range(InputCell).Select
End Sub

Private Function CheckAllCapital(CheckString As String) As Boolean
    If (UCase(CheckString) = CheckString) Then
        CheckAllCapital = True
    Else
        CheckAllCapital = False
    End If
End Function
------------------------------------------------------------------------

The error occurs on Parser.

ThisWorkbook contains:
------------------------------------------------------------------------
Public Sub Workbook_Open()
    Application.Run "Parser.xls!ReInput"
End Sub
------------------------------------------------------------------------

Sheet1 (Parser) contains:
------------------------------------------------------------------------
Private Sub RunButton_Click()
    Run
End Sub
------------------------------------------------------------------------

Only "Sheets("Parser").Select" is highlighted. Nothing happens when cursor is held over text.

The error number is definitely 32809.
0
antratCommented:
Check the spelling of the sheet "Parser" and to be sure re-type the name of the sheet.

are you getting the:
MsgBox "Please input the name of the data file before pressing the Run button.", vbOKOnly, "Warning"
         

antrat
0
lhuttonAuthor Commented:
The sheet is Parser. I don't get the error message as the macro fails to execute in the first place. It doesn't allow the Run button to be pressed after entering a file path (ie. the button is dead).
0
NoggyCommented:
There's a few things I can mention:
1. Check that the settings in the VB Editor's Tools-Options are the same as those on the other PCs. Especially the Break On All Errors option button in the General tab. To get to the VB Editor from Excel, press Alt+F11.
2. Are there any AddIns in Excel on this PC that aren't on the others? If so, remove them (possibly from your XLStart directory) and try again.
3. In your Start Menu, select Run and enter the following command line to launch Excel:
Excel.exe /regserver
This will rebuild the registry settings for Excel.
0
argmysterCommented:
Press Alt + F11 to get into the VBA Editor, mouse over to Tools, click, mouse down to References.

Now, this is where you should find  your problem.  Look for anything in there that is missing.  If it is missing it will have have the word missing next to the reference.

If everything is ok and there are no missing references, then try this.

Close all workbooks and in a 'New' workbook, enter the VB Editor, insert a module.

Again, goto Tools, References and make sure you have a checkmark in the following reference:

Microsoft DAO 3.X Object Library

Noet: the X is for what ever version you may have to reference.  It could be 3.0 or 3.5 depending on what is installed or has ever been installed on your machine (like VB5 or VB6).

Once you have the reference checked enter this code in that new module you just inserted.

Sub Testing()
Msgbox dbengine.version
end sub

Now run that Testing macro.

If you get a message box indicating the version then you are lucky, because your DAO isn't hosed up.

If you get a runtime error, then you have got problems. Major problems!

This could be why it works on other machines and not this one.  Which tells me it ain't the file, its the system.

Let me know and I will give you more help at this point.

Good Luck!
Argmyster...
0
argmysterCommented:
The first part of my comment you need to have the problem file open in Excel to see if you have missing Reference.

Sorry I failed to mention that.
Argmyster...
0
lhuttonAuthor Commented:
OK, I'm at the first part of your comment - there are five checked references and about 40 unchecked. I don't know whether anything is missing???

About inserting a module - how do I do that??? This workbook and its macros were created for me; I don't know anything about VB, sorry.
0
argmysterCommented:
If you have opened your troubled workbook and you looking at the References for that workbook, and there are none that are checked that say they are missing then you are ok with that part.

Now.  After you have closed all workbooks and have a new workbook, while you are in the VB Editor, click on Insert, mouse down to Module and click on it.  That will insert a module for your new workbook.

At that point and after you have went back to the References in Tools and checked the Reference I explained (Microsoft DAO 3.X Object Library)
then you can enter the code I gave you in the new module and run it.

If you get a runtime error let me know.

Argmyster...

0
lhuttonAuthor Commented:
I have
Microsoft DAO 3.0 Object Library
and
Microsoft DAO 3.51 Object Library

I put a check in 3.51 and ran the code like you said. I got an error message:
Run-time error '429':
ActiveX component can't create object
0
argmysterCommented:
Ok, now take the check out of 3.51 and put a check in the 3.0 and run the code again.

Argmyster...
0
lhuttonAuthor Commented:
Same error :-(
0
argmysterCommented:
One problem could be if Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 or 6.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic.

NOTE: The same licensing key is also required and added by Visual Basic
5.0 and 6.0. Because Visual Basic adds the same DAO design-time licensing key
required by Excel, the problem does not occur if Visual Basic is installed.

I suspect the line of code:

Application.Run "Parser.xls!ReInput"
 
is making a call to the DAO design-time licensing key and now that we have determined that your DAO is not functioning properly you need to fix it on that machine.

Trust me-it ain't easy to do this.  You basically have two options.

One, I can give you the Registry Keys you need to create in the Registry and you can try that or if that don't work...

Two, you have to completely remove all of Office 97 and all of DAO on your computer.

Which one you want to try?  

But before you answer this while you are in normal Excel, not the VB Editor, goto Help, About Microsoft Excel and tell me if you see at the top this

Microsoft Excel 97 SR-1 or 2

If you don't have the SR 1 or 2 installed you will need to do that before we do anything with the Registry.

Argmyster...
0
lhuttonAuthor Commented:
I have the second service release. I think I might just leave things as they are and stick to using Excel 97 on another machine. When I've tried reinstalling Office again before, I keep getting messages saying files are missing on the CD. I think I'll just have to wait until I can afford to buy a new comp. with better software.
0
argmysterCommented:
Well, we can put the registry keys in you need to try and fix this problem.  It isn't going to make things any worse and I have fixed this problem with the new key.

It's up to you.  It really isn't that hard.

Here is the info to get the Key entered properly.

This is what Microsoft recommends you to do.

WARNING: Using Registry Editor incorrectly can cause serious problems that
may require you to reinstall your operating system. Microsoft cannot
guarantee that problems resulting from the incorrect use of Registry Editor
can be solved. Use Registry Editor at your own risk.

Backup your registry before you do this by exporting it to your hard drive in a place you can easily find.

Step-by-Step Instructions
-------------------------

1. From the Start menu, choose Run, enter "regedit", and press the ENTER
   key. The Registration Editor appears.

2. In the Registration Editor, browse to HKEY_CLASSES_ROOT\LICENSES.

3. From the Edit menu, select New, then choose Key. A new registry key
   appears.

4. Enter "F4FC596D-DFFE-11CF-9551-00AA00A3DC45" (without quotes) as the
   name of the new key.

5. In the right-hand window pane, double-click on the Default entry for the
   key you created.

6. Enter "mbmabptebkjcdlgtjmskjwtsdhjbmkmwtrak" (without quotes) as the
   Value Data, and press enter.

7. If you need to update multiple PCs, create a REG file for that key by
   choosing 'Export Registry File' from the Registry menu. Then, copy the
   REG file to other PCs and double-click on it to add the key to the
   registry.

8. Choose Exit from the Registry menu to close the Registration Editor.

STATUS
======

Microsoft has confirmed this to be a problem in Microsoft Excel 97. We are
researching this bug and will post new information here in the Microsoft
Knowledge Base as it becomes available.

Good Luck!

Argmyster...
0
lhuttonAuthor Commented:
Can you post a URL for me to refer to the article in the Knowledge Base, please?
0
argmysterCommented:
0
lhuttonAuthor Commented:
OK, I added the key and rebooted the computer. When I loaded the file, the original error still appears. What next?
0
argmysterCommented:
Try the little test I gave you, new workbook, insert module, add

Sub Testing()
msgbox dbengine.version
end sub

If you still get the runtime error, then you don't have any other choice but to remove all of office using

http://support.microsoft.com/support/kb/articles/q158/6/58.asp

Get the utilities:

Utility to Completely Remove Remaining Office 97 Files

and...

RegClean 4.1a

These utilities can be obtained from Q158658, the URL I gave you.

One step it don't talk about is removing DAO from your machine.

That is in this directory:
 
C:\Program Files\Common Files\Microsoft Shared\DAO

You will have to delete the DAO folder.

Then you install Office 97 again, install the SR's and hope that fixes the problem.

As I stated earlier, this DAO thing is a real tough one to fix.  In some cases and I know this seems hard to believe, you may even have to FDISK, Install Windows and then Office 97 then the SR's and then you can install the rest of your stuff.

It can be that messed up.  Microsoft really screwed up with DAO.





0
argmysterCommented:
One last ditch thing you can try is this

Get your Office97 CD and run this program from

D:\VALUPACK\DATAACC

Dataacc.exe

Run it.  It shouldn't hurt anything, and it may fix your problem.

Good Luck!

Argmyster...
0
lhuttonAuthor Commented:
Error still occurs. I may consider reinstalling everything some time. Thank you for all your help.
0
lhuttonAuthor Commented:
Oh ^&*()&, wrong person. Hang on, I'll see if Customer Care can help...
0
argmysterCommented:
This article may help also,

http://support.microsoft.com/support/kb/articles/Q163/4/75.asp

This is my last comment.

Argmyster...
0
ianBCommented:
Hi,

I have been directed to this question by lhutton.

argmyster, I have issued a question in this topic are for you to claim your points.

Ian
Community Support @ Experts Exchange
0
lhuttonAuthor Commented:
OK, so I've decided to uninstall Office 97. What a task! I've finished uninstalling and removing files and registry keys and using the utilities and am now ready to install afresh :-)

Before I do, I want to make sure I do it properly...
What options do I need to choose, beyond a typical install???
0
argmysterCommented:
Did you get rid of the DAO folder?

If you didn't do so now.

Before you install if you are using Win95/98 press CTRL + ALT + DEL and end the task on all programs except
Systray & Explorer

Once you done that you can put the CD in and run setup or wait for autorun to start the install.

What ever you do, make sure only one install is running.

Now for the install.  When you install Office make sure you add the database components.  I can't remember what they are at the moment but you will need to install more than just a typical.

Use the custom setup and just select all, you can always remove items if you don't want them.  I say this because I am not sure exactly what items you need to select.  It is a lot easier to remove and item then it is to install it especially after you have the SR-1 & SR-2 installed.  Trust me, if you have the room, install ALL OF OFFICE97 on your hard drive.

Again, I must remind you, depending on what sort of DAO was on your machine, this may or may not completely fix your problem.  Remember your registry entries that may not have been removed referencing some DAO component you may have on this machine.  And remember also, this problem is because of a verson of DAO that was already on this machine when Office was first installed, that's why you got the runtime error with our Testing Macro.  Office failed to write the proper registry keys because of a conflict in thier silly way of installing stuff relating to shared DAO components.  It sucks and everyone knows it sucks.

Good Luck!  Run Regcleaner as much as you need to make sure it fixes all that it can.  Just one run of Regcleaner will not always fix problems.  It fixes on and then it can find another the next time you run it, just because it fixed one the previouse run.  Strang, but remember we are dealing with Microsoft here.

Argmyster...
0
lhuttonAuthor Commented:
OK, have done everything recommended and fully installed Office 97 again. But, alas, the error remains :-( Microsoft Exchange isn't loading on StartUp anymore but I'm probably worse off with all this extra stuff than I was. Guess I should have just left it.
0
agent327Commented:
I have received the same error in one of my applications.
My meaning it has to do with not loading an ActiveX-object, when loading the workbook. When I open this Workbook and try to run a macro, I get the 32809-error. But when I open the same Workbook and just click on the Form that should be opened by the macro and the start the macro, the program runs without any errors.
You could try to load a form when opening the Workbook, this will initialise your controls and it will run without the error.
0
lhuttonAuthor Commented:
Will have to review this question in more detail; was so long ago, I'm not sure the problem even exists anymore :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.