Error 424 & CreateObject("Excel.Sheet") 1

[MePenguin]

My app has developed an Error 424 "Object required" on the line marked below, but only on my WinXP Home developing machine:

Dim ObjExcel As Object
Dim ObjNewSheet As Object

Set ObjExcel = CreateObject("Excel.Sheet") '*error here

It worked a few weeks ago, and as far as I know I have not done nothing with this part of the program since then.

Restart/reboot doesn't help.

It works on my Win2K and another XP Home machine without a problem.

Anybody encountered this, and can anyone tell me how to go about debugging it?

Phil

---------------
Pass me the ether.

 

[Steve3110]

Might sound like a silly question buy have you got the same version of office installed on all the computers?

Also, have you tried the code with excel running in the background?

 

[ThomasLafferty]

You probably need to return a reference to a library. What is your development platform?

Tom

Live once die twice; live twice die once.

 

[MePenguin]

Steve3110: reasonable question, answer = yes.
Haven't tried with Excel in the background - other bits of the routines will not work if Excel is open when run, but I suppose I could circumvent that & try.

Tom: WinXP Home, Office 2000DEV. Do you mean that it might have forgotten a library location, or corrupted one? All references appear to be connected in the Tools/references window... same on all 3 machines, as follows:

Reference: VBA
Location: C:\Program\Delade filer\Microsoft Shared\VBA\VBA6\VBE6.DLL

Reference: Access
Location: C:\Program\Microsoft Office\Office\MSACC9.OLB

Reference: DAO
Location: C:\Program\Delade filer\Microsoft Shared\DAO\DAO360.DLL

Reference: stdole
Location: C:\WINDOWS\System32\stdole2.tlb

Reference: ADODB
Location: C:\Program\Delade filer\System\ado\msado21.tlb

Reference: MSComDlg
Location: C:\WINDOWS\SYSTEM32\comdlg32.ocx

Reference: VBIDE
Location: C:\Program\Delade filer\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Reference: Excel
Location: C:\Program\Microsoft Office\Office\EXCEL9.OLB

Reference: Scripting
Location: C:\WINDOWS\SYSTEM32\SCRRUN.DLL

Reference: MSComctlLib
Location: C:\WINDOWS\System32\MSCOMCTL.OCX

Ideas?

I'm reluctant to reinstall Office...

Phil

---------------
Pass me the ether.

 

[MePenguin]

Tom: WinXP Home SP1. (Other test machine is XPHome SP2... the development machine is too critical at the moment for me to risk installing SP2)

Phil

---------------
Pass me the ether.

 

[ThomasLafferty]

Hmmmm.... well if your libraries are all loaded, and the same on all three machines, then I suspect corruption. You could try changing the timing of binding the object variable from late to early binding. Here's a bit of an explanation from Access VBA help:

Declaring an object variable with the As Object clause creates a variable that can contain a reference to any type of object. However, access to the object through that variable is late bound; that is, the binding occurs when your program is run. To create an object variable that results in early binding, that is, binding when the program is compiled, declare the object variable with a specific class ID. For example, you can declare and create the following Microsoft Excel references:

Dim xlApp As Excel.Application 
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
[code]

If changing the timing of the binding doesn't help (and I suspect it won't) try Help>Detect and Repair.

If that doesn't do it, you may need to reinstall Office...

Hope this helps.
Tom

[COLOR=green]Live once die twice; live twice die once.[/color]

 

[MePenguin]

Thanks Tom, I was considering the binding timing, although am reluctant incase there are any cascading consequences - could there be? Or am I jsut generally being too reluctant about everything today?

This may be symtomatic of greater Office problems though, since my Office help failed a while back (Access went first, and then the rest!)

I'll try your tips before reinstalling though.

Regards,

Phil

---------------
Pass me the ether.

 

[ThomasLafferty]

Well, I understand caution - lack of it can be painful. Make a backup of your project, then Nuke away!
Tom

Live once die twice; live twice die once.

 

[MePenguin]

Many, many backups in far away places

Late binding worked - and I'd love it if you could explain why! Please?

It also seems to be a lot faster, so maybe this was all for the best. After all I still have some hair left...

I ran a repair install on Office & got my help back just for good measure.

Thanks, and have a star Tom!

Phil

---------------
Pass me the ether.

 

[ThomasLafferty]

Thanks for the star!

Let's call it a lucky guess as to why it worked. I have seen in the past that performance issues with Access dbs can sometimes be resolved by decompiling and recompiling the program, so it occurred to me that there are probably some program level issues with compilation that I know nothing about. For instance, you will probably notice a marked decrease in your db size even beyond what you would find if you compacted and repaired it if you decompile first, then compact and repair. I would love to get my hands on a decent explanation for this, but I suspect it has something to do with how Windows based files become fragmented.

To test this, make a backup of your db (assume you already have, eh?).
Close your db, and note its file size from Explorer.
Click Start>Run
Enter the full path to your db in quotes and follow it with \decompile

"C:\[!]Your Directory Path[/!]\YourDB.mdb" \decompile

While holding down your shift key, click OK
Your db should open
While holding down your shift key, Click Tools>Database Utilities>Compact and repair database
Your db should open again
Close it and check file size in Explorer

Should be quite a bit smaller...

This probably gets nowhere near answering your question, but it's good info.

I googled late binding and found this article pertaining to OOP:

http://www.ftponline.com/vsm/2003_12/magazine/features/reynolds/

Hope it helps.

Thanks again for the star!



Live once die twice; live twice die once.

 

[MePenguin]

Thank's Tom, I'll try that, and read the other.

MSKB has a reasonable binding explanation too:
[URL unfurl="true"]http://support.microsoft.com/kb/245115/EN-US/[/url]

Sounds like it could have been a compatibility issue between versions... although of what, and why it only started recently I have no idea. I blame Windows Update, as I do whenever anything stops working! (OK, maybe a bit unfair?)

You deserved the star

Phil

---------------
Pass me the ether.

 

[MePenguin]

A quick update - no file size change on following your decompile tip, Tom.

I'll remember the tip anyway, thanks.

Phil

Phil

---------------
Pass me the ether.