Sorry for the confusion (including that the error code s/b '430'). I am saying that the recordset is populating as I am getting past the 'If Not (rs.EOF And rs.BOF) Then' code.
Excel then opens with all of the column headers and the tab is renamed to 'Search Results'
But none of the data populates to the spreadsheet beyond that and I get the following message:
"Run-time error '430': / Class does not support Automation or does not support expected interface" associated with the following line of code:
My main question to you is: Is this line correct/complete: 'Set rs = db.OpenRecordset(strSQL)' or should I add Type or Options? I'm not having any problems here but just want to know proper code wrt DAO.
Beyond that I will check with our computer administrators today and see if they can do something about the '430' run-time error, unless anyone has suggestions on how I may be able to correct or get around this error?
Dim strVIEW As String
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQL As String
Dim strMsg As String
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPathFileName As String, strWorksheetName As String
Dim strRecordsetDataSource As String
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Set db = CurrentDb
strSQL = strSELECT & Chr(13) & strFROM & Chr(13) & strWHERE & Chr(13) & "ORDER BY " & strVIEW & ".AssetID DESC, " & strVIEW & ".MoveID DESC;"
Set rs = db.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
blnEXCEL = False
strPathFileName = "C:\PARTS.xls"
blnHeaderRow = True
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
On Error GoTo 0
xlx.Visible = True 'workbook visible when the code is running (set to false if not desired)
Set xlw = xlx.Workbooks.Add
Set xls = xlw.Worksheets(1)
xls.Name = "Search Results"
Set xlc = xls.Range("A1")
' Set db = CurrentDb()
' Write the header row to worksheet
If blnHeaderRow = True Then
For lngColumn = 0 To rs.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rs.Fields(lngColumn).Name
Set xlc = xlc.Offset(1, 0)
' copy the recordset's data to worksheet
xlc.CopyFromRecordset rs '<<<<<<<<<<<<<'430' Runtime Error Code!!!
Set rs = Nothing
Set db = Nothing
Set xlc = Nothing
Set xls = Nothing
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
MsgBox "No records were found that matched the given search parameters.", 0, "PARTS Database Message"
Set rs = Nothing