OLE Automation Works on One PC Not Another

[orcinus]

I have some Automation code as pasted below that works on PC 1, but not on PC 2.

It starts to do the code, but then bombs on the line:

.range("A2").CopyFromRecordset rs

The error i get on PC2 is: Class Does Not Support Automation or does not support expected Interface.

I have checked my References and they appear to be the same on both PC's (the names are the same and the addresses are the same)..

Obviously there is something else I am missing. Any help is appreciated..!

CODE:

Public Function fcnExport()
On Error GoTo Err_cmdExporttoExcel_Click
Dim automApp As Excel.Application
Dim xlWksht As Excel.Worksheet
Dim xlWkbook As Excel.Workbook
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim strPath As String
Dim strFP As String 'file path
Dim strFN As String 'file rpt name
Dim strDT As String 'file name date tag
Dim strFE As String 'file extention
Dim lngRecCount As Long
Dim iCols As Integer
Set db = CurrentDb
Set automApp = CreateObject("Excel.Application")
'strPath = CurrentProject.Path
strFP = "C:\"
strFN = "6481_IFP_Rpt_Card_"
strDT = Format(Date, "yyyymm")
strFE = ".xls"
strPath = strFP & strFN & strDT & strFE
strSQL = "Select * from qry_output_Metric_Final"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.MoveLast
lngRecCount = .RecordCount
.MoveFirst
End With
With automApp
.Workbooks.Add
.DisplayAlerts = False
.Visible = True

For iCols = 0 To rs.Fields.Count - 1
.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name 'changed ".cells(2, icols + 1)" from 2 to 1
Next

.Cells.Range("A1:G1").Font.Bold = True
.Columns.Range("A:G").HorizontalAlignment = xlCenter
.Columns.Range("F1:F7").HorizontalAlignment = xlLeft
.Cells.Range("A1:A2").Interior.Color = 12632256
.Cells.Range("B1:B2").Interior.Color = 8421631
.Cells.Range("C1:C2").Interior.Color = 16776960
.Cells.Range("D12").Interior.Color = 16744703
.Cells.Range("E1:E2").Interior.Color = 16744448
.Cells.Range("F1:G2").Interior.Color = 33023

.Range("A2").CopyFromRecordset rs
.Range(.Range("F1:G1"), .Range("F1:G1").End(xlDown)).Interior.Color = 33023

.Range("A1", .Range("G1").End(xlDown)).Select

.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With .Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

.Columns.AutoFit
.ActiveWorkbook.SaveAs FileName:=strPath
End With
Exit_cmdExporttoExcel_Click:
On Error Resume Next
rs.Close
Set rs = Nothing


Set db = Nothing
automApp.Quit
Exit Function
Err_cmdExporttoExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExporttoExcel_Click

End Function

[rpeare]

Are both computers using the same version of access? Are both computers using the same database or front end, or are they using different front ends?

Have you tried

.range("A2:A2").CopyFromRecordset rs

[orcinus]

Are both computers using the same version of access? Are both computers using the same database or front end, or are they using different front ends?

Have you tried

.range("A2:A2").CopyFromRecordset rs

Yes, they are both using the same version of Access (2003:11.8321.8333 SP3) and I'm running the code from within the module window on both PC's. Both have the same version of Excel as well..(these computers are on a network and received the exact same configuaration - outside of any configurations that are by nature local to the PC - as this appears to be?)

I also tried you're suggestion, to no avail. Again, it runs fine from one computer which leads me to believe its not the code..

Any suggestions on what might be a local configuration difference between the two PC's? Or something else?

Thanks..

[rpeare]

http://www.pcreview.co.uk/forums/err...-t1604350.html

Fellow seems to have the same problem but fixed it. Are the two machines on different service packs?

[orcinus]

http://www.pcreview.co.uk/forums/err...-t1604350.html

Fellow seems to have the same problem but fixed it. Are the two machines on different service packs?

No, when I chose "About", for both PC's it shows SP3..

Thanks..

[rpeare]

Maybe try copying the DLL from the computer that works to the one that doesn't and see if that helps? I am kind of shooting in the dark as I've not encountered this problem before

[orcinus]

Maybe try copying the DLL from the computer that works to the one that doesn't and see if that helps? I am kind of shooting in the dark as I've not encountered this problem before

I copied all the files from the PC that worked with the exception of stdole2.tlb (looks like this is a system file that resides on C:\WINDOWS\system32\, said this was being used so couldn' copy over it or change its name, I'm guessing its used by explorer)..

Anyway, I rebooted the PC and still didn't work. As I mentioned, all these PC's are supposed to have the same image.

Any other suggestions?

Thanks..