ActiveX component can't create object. Works in one workbook but not another?

I have a macro in Excel 2007 VBA (that was originally created in 2003).
In spreadsheet 1 it works fine even when I run it in 2007 Excel. This is 2003 workbook .xls but has macros
The 2007 created spreadsheet is .xlsm.
The code is attached.
I have matched the references b/w the two workbooks... but when I run it in spreadsheet 2 it gives the following error:
Run Time Error 429
Active X component cant create object

Note the macro pulls data from MS Access 2003 database.

And even more strangely it worked yesterday but not today.

the row that breaks is:


Sub Execute_Retrieve_ETO_OpenPosition()
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stDB As String, stSQL As String, stConn As String
    Dim wsSheet As Worksheet
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set wsSheet = ThisWorkbook.Worksheets("HedgeBookOpenPosition")
    stDB = "T:\Cotton\Position Report\CTC_DatabaseII.mdb"
     'Since the database is passwordprotected we need to
     'add the password to get access to it.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
     'The name of the stored question is placed between the brackets.
    stSQL = "qryOptionsETO_Open_PNL_ByBook_ByMonth"
    cnt.Open stConn
    rst.Open stSQL, cnt
    wsSheet.Cells(5, 2).CopyFromRecordset rst
    Set rst = Nothing
    Set cnt = Nothing
End Sub

Open in new window

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.

Brad Sims, CCNANetwork AdministratorCommented:
Try removing the word "set"

wsSheet = ThisWorkbook.Worksheets("HedgeBookOpenPosition")
h2walshAuthor Commented:
didnt fix anything..

the strange thing is the exact same code works on another spreadsheet (workbook)...

Rory ArchibaldCommented:
Are you sure it's that line that gives the error and not the previous one?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

h2walshAuthor Commented:
yes I am sure
the problem seems to be this.
If I save the workbook as Excel 2003 the macro runs
if I save as macro enabled 2007 workbook it does not.

So the short term solution is I save it in the old version.. but I would still like to solve the  problem.. :)

Rory ArchibaldCommented:
Can you post the workbook without any data, but with structure intact?
Brad Sims, CCNANetwork AdministratorCommented:
I agree with rorya...I didn't get an error stepping through the code at this line in 2007 so I need to see a sample worksheet to better diagnose the problem.
h2walshAuthor Commented:
will try and remove data and get the worksheet attached
h2walshAuthor Commented:
I cannot attach the file as is because its extension is .xlsm. Which is not allowed as per the list of accepted extensions...
I have saved it in 2003-07 format and still get the same error.. so you might be able to work out what is wrong with it.

Note i have found an interim solution. I exported the required module to a new 2003 file and it works... so its something in the .xlsm files that is causing the issue

Rory ArchibaldCommented:
Your workbook is corrupt. I suggest you rebuild it a.s.a.p.!
Brad Sims, CCNANetwork AdministratorCommented:
Ok, I see the same error you're receiving now.  I changed that line to

Set wsSheet = Worksheets("HedgeBookOpenPosition")

and it let me step through the code.
h2walshAuthor Commented:
already rebuilt.. the problem I am trying to solve is how do you transfer 2003 created macros into a 2007 workbook?
Rory ArchibaldCommented:
Either copy and paste or use the Convert option in 2007.
I presume you mean you have rebuilt since posting that one?
Rory ArchibaldCommented:
Either copy and paste or use the Convert option in 2007.
I presume you mean you have rebuilt since posting that one?
h2walshAuthor Commented:
Yes I created a new 03 worksheet and started again a few days ago and that seems to have averted the problem

Consider the problems solved.. I think it just must have been corrupted.

thanks for all your help!!

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
Rory ArchibaldCommented:
If you look at the workbook project in the VBEditor you can see it's corrupt - you have two copies of ThisWorkbook for a start!
Glad it's sorted now.
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 Access

From novice to tech pro — start learning today.