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

Hi,
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:
ThisWorkbook.Worksheets("HedgeBookOpenPosition")



HW

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"
     
    wsSheet.Range("b5:j500").EntireRow.Clear
    cnt.Open stConn
    rst.Open stSQL, cnt
     
    wsSheet.Cells(5, 2).CopyFromRecordset rst
     
    rst.Close
    Set rst = Nothing
    cnt.Close
    Set cnt = Nothing
    
    Execute_Retrieve_OTC_OpenPosition
    Execute_Retrieve_ETO_HedgeBookRealised
    Execute_Retrieve_OTC_HedgeBookRealised
End Sub

Open in new window

h2walshAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
h2walshConnect With a Mentor Author 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!!
0
 
Brad Sims, CCNANetwork AdministratorCommented:
Try removing the word "set"

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

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

thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rory ArchibaldCommented:
Are you sure it's that line that gives the error and not the previous one?
0
 
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.. :)

0
 
Rory ArchibaldCommented:
Can you post the workbook without any data, but with structure intact?
0
 
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.
0
 
h2walshAuthor Commented:
will try and remove data and get the worksheet attached
0
 
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


ExpertExchangeFile.xls
0
 
Rory ArchibaldCommented:
Your workbook is corrupt. I suggest you rebuild it a.s.a.p.!
0
 
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.
0
 
h2walshAuthor Commented:
already rebuilt.. the problem I am trying to solve is how do you transfer 2003 created macros into a 2007 workbook?
0
 
Rory ArchibaldCommented:
Either copy and paste or use the Convert option in 2007.
I presume you mean you have rebuilt since posting that one?
0
 
Rory ArchibaldCommented:
Either copy and paste or use the Convert option in 2007.
I presume you mean you have rebuilt since posting that one?
0
 
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.
0
All Courses

From novice to tech pro — start learning today.