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

%3CLINGO-SUB%20id%3D%22lingo-sub-1716222%22%20slang%3D%22en-US%22%3EError%20number%20438%20Object%20doesn't%20support%20tis%20property%20or%20method%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1716222%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20know%20if%20anyone%20will%20be%20able%20to%20help%20me%20or%20not.%20I%20have%20recently%20taken%20over%20the%20programming%20and%20management%20of%20a%20database%20for%20a%20local%20charity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20new%20vba%20code%20to%20create%20a%20excel%20spreadsheet%20base%20on%202%20queries.%20I%20have%20used%20a%20vba%20code%20from%20another%20coding%20in%20the%20same%20database%20(that%20the%20last%20person%20created)%20and%20changed%20the%20names%20of%20the%20excel%20workbook%20and%20worksheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20am%20getting%20Error%20numer438Object%20doesn't%20support%20this%20property%20or%20method.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdDonatedSpreadsheet_Click()%3CBR%20%2F%3EDim%20appXL%20As%20Excel.Application%3CBR%20%2F%3EDim%20wbDonated%20As%20Excel.Workbook%3CBR%20%2F%3EDim%20wsDonated%20As%20Excel.Worksheet%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20strDesktop%20As%20String%3CBR%20%2F%3EDim%20DB%20As%20DAO.Database%3CBR%20%2F%3EDim%20rec%20As%20DAO.Recordset%3CBR%20%2F%3EDim%20f%20As%20DAO.Field%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3EDim%20j%20As%20Integer%3CBR%20%2F%3EDim%20k%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%20errHandler%3CBR%20%2F%3E'DoCmd%20.Hourglass%20True%3CBR%20%2F%3E'DoCmd%20.Minimize%3CBR%20%2F%3ESet%20appXL%20%3D%20New%20Excel.Application%3CBR%20%2F%3ESet%20DB%20%3D%20CurrentDb()%3CBR%20%2F%3EappXL.Visible%20%3D%20True%3CBR%20%2F%3ESet%20wbDonated%20%3D%20appXL.Workbooks.Add%3CBR%20%2F%3EwbDonated.Activate%3CBR%20%2F%3EFor%20k%20%3D%201%20To%202%3CBR%20%2F%3E'loop%20round%20two%20sheets%3CBR%20%2F%3EIf%20k%20%26gt%3B%201%20Then%3CBR%20%2F%3EwbDonated.Sheets.Add%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ESet%20wsDonated%20%3D%20wbDonated.DonatedSheet%3CBR%20%2F%3ESelect%20Case%20k%3CBR%20%2F%3ECase%201%3CBR%20%2F%3EwsDonated.Name%20%3D%20%22DonatedActiveFriend%22%3CBR%20%2F%3ESet%20rec%20%3D%20DB.OpenRecordset(%22qryActiveDonated%22)%3CBR%20%2F%3EwsDonated.Range(%22A2%22).Select%3CBR%20%2F%3EappXL.ActiveWindow.FreezePanes%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3ECase%202%3CBR%20%2F%3EwsDonated.Name%20%3D%20%22NotDonatedActiveFriend%22%3CBR%20%2F%3ESet%20rec%20%3D%20DB.OpenRecordset(%22qryActiveNotDonated%22)%3CBR%20%2F%3EwsDonated.Range(%22A2%22).Select%3CBR%20%2F%3EappXL.ActiveWindow.FreezePanes%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20rec%3CBR%20%2F%3E.MoveFirst%3CBR%20%2F%3Ei%20%3D%201%20'row%3CBR%20%2F%3Ej%20%3D%201%20'column%3CBR%20%2F%3EFor%20Each%20f%20In%20.Fields%3CBR%20%2F%3EwsDonated.Cells(i%2C%20j).Value%20%3D%20f.Name%3CBR%20%2F%3Ej%20%3D%20j%20%2B%201%3CBR%20%2F%3ENext%20f%3CBR%20%2F%3EDo%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3Ej%20%3D%201%3CBR%20%2F%3EFor%20Each%20f%20In%20.Fields%3CBR%20%2F%3EwsDonated.Cells(i%2C%20j).Value%20%3D%20f.Value%3CBR%20%2F%3Ej%20%3D%20j%20%2B%201%3CBR%20%2F%3ENext%20f%3CBR%20%2F%3E.MoveNext%3CBR%20%2F%3ELoop%20Until%20.EOF%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3Erec.Close%3CBR%20%2F%3ENext%20k%3CBR%20%2F%3EDB.Close%3CBR%20%2F%3E'strDesktop%20%3D%20%22C%3A%5CUsers%5CFOTYH%5CDesktop%5CDonated.xlsx%3CBR%20%2F%3EstrDesktop%20%3D%20%22C%3A%5CUsers%5CUser%5CDesktop%5CDonated.xlsx%22%3CBR%20%2F%3E%3CBR%20%2F%3EStartfile%3A%3CBR%20%2F%3EwbDonated.SaveAs%20strDesktop%3CBR%20%2F%3E'DoCmd.Hourglass%20False%3CBR%20%2F%3EMsgBox%20%22Donated%2FNotDonated%20Spreadsheets%20Created%20On%20Desktop!%22%3CBR%20%2F%3EwbDonated.Close%3CBR%20%2F%3EappXL.Quit%3CBR%20%2F%3E'DoCmd.Maximize%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EerrHandler%3A%3CBR%20%2F%3E'If%20Err%20%3D%2076%20Then%3CBR%20%2F%3EIf%20Err%20%3D%201004%20Then%3CBR%20%2F%3EstrDesktop%20%3D%20%22C%3A%5CUsers%5CFOTYH%5CDesktop%5CDonated.xlsx%22%3CBR%20%2F%3EResume%20Startfile%3CBR%20%2F%3EElse%3CBR%20%2F%3EMsgBox%20%22Error%20number%20%22%20%26amp%3B%20Err%20%26amp%3B%20Err.Description%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1716222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1723799%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20number%20438%20Object%20doesn't%20support%20tis%20property%20or%20method%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1723799%22%20slang%3D%22en-US%22%3EWhen%20you%20select%20to%20debug%20the%20error%2C%20which%20line%20of%20code%20is%20highlighted%20as%20being%20problematic%3F%3C%2FLINGO-BODY%3E
Highlighted
New 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
wbDonated.Activate
For k = 1 To 2
'loop round two sheets
If k > 1 Then
wbDonated.Sheets.Add
End If
Set wsDonated = wbDonated.DonatedSheet
Select Case k
Case 1
wsDonated.Name = "DonatedActiveFriend"
Set rec = DB.OpenRecordset("qryActiveDonated")
wsDonated.Range("A2").Select
appXL.ActiveWindow.FreezePanes = True

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

End Select

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

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

Startfile:
wbDonated.SaveAs strDesktop
'DoCmd.Hourglass False
MsgBox "Donated/NotDonated Spreadsheets Created On Desktop!"
wbDonated.Close
appXL.Quit
'DoCmd.Maximize
Exit Sub

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

3 Replies
Highlighted
When you select to debug the error, which line of code is highlighted as being problematic?
Highlighted
You may also like to implement something like https://www.devhut.net/2012/04/19/ms-access-vba-export-records-to-excel/ which is a reusable function that you simply call whenever you need to export data.
Highlighted

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