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:
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"
rst.Open stSQL, cnt
wsSheet.Cells(5, 2).CopyFromRecordset rst
Set rst = Nothing
Set cnt = Nothing