Error number 438 Object doesn't support tis property or method

Copper Contributor

I don't know if anyone will be able to help me or not. I have recently taken over the programming and management of a database for a local charity.


I am trying to create a new vba code to create a excel spreadsheet base on 2 queries. I have used a vba code from another coding in the same database (that the last person created) and changed the names of the excel workbook and worksheets. 


But I am getting Error numer438Object doesn't support this property or method.


I hope this makes sense.


Private Sub cmdDonatedSpreadsheet_Click()
Dim appXL As Excel.Application
Dim wbDonated As Excel.Workbook
Dim wsDonated As Excel.Worksheet

Dim strDesktop As String
Dim DB As DAO.Database
Dim rec As DAO.Recordset
Dim f As DAO.Field
Dim i As Integer
Dim j As Integer
Dim k As Integer

On Error GoTo errHandler
'DoCmd .Hourglass True
'DoCmd .Minimize
Set appXL = New Excel.Application
Set DB = CurrentDb()
appXL.Visible = True
Set wbDonated = appXL.Workbooks.Add
For k = 1 To 2
'loop round two sheets
If k > 1 Then
End If
Set wsDonated = wbDonated.DonatedSheet
Select Case k
Case 1
wsDonated.Name = "DonatedActiveFriend"
Set rec = DB.OpenRecordset("qryActiveDonated")
appXL.ActiveWindow.FreezePanes = True

Case 2
wsDonated.Name = "NotDonatedActiveFriend"
Set rec = DB.OpenRecordset("qryActiveNotDonated")
appXL.ActiveWindow.FreezePanes = True

End Select

With rec
i = 1 'row
j = 1 'column
For Each f In .Fields
wsDonated.Cells(i, j).Value = f.Name
j = j + 1
Next f
i = i + 1
j = 1
For Each f In .Fields
wsDonated.Cells(i, j).Value = f.Value
j = j + 1
Next f
Loop Until .EOF

End With
Next k
'strDesktop = "C:\Users\FOTYH\Desktop\Donated.xlsx
strDesktop = "C:\Users\User\Desktop\Donated.xlsx"

wbDonated.SaveAs strDesktop
'DoCmd.Hourglass False
MsgBox "Donated/NotDonated Spreadsheets Created On Desktop!"
Exit Sub

'If Err = 76 Then
If Err = 1004 Then
strDesktop = "C:\Users\FOTYH\Desktop\Donated.xlsx"
Resume Startfile
MsgBox "Error number " & Err & Err.Description
Exit Sub
End If
End Sub

3 Replies
When you select to debug the error, which line of code is highlighted as being problematic?
You may also like to implement something like which is a reusable function that you simply call whenever you need to export data.

@Daniel_Pineault It doesn't let me run / select debug it does comes up with the error