Error 438: Object doesn't support this property or method

[StuLux]

When I run my code (which opens Excel from Access) it does everything it is supposed to but I then get the above error but I do not get the error if I open the VBA window and then run the code. (The code is password protected, not sure if this is relevenat though).

Can anybody explain why this might be and what this error might be referring to?

 

[RoryA]

What is the code?

 

[Norie]

Stuart

It could be referring to many things.

Hard to tell what without seeing the code though.

 

[StuLux]

OK! You asked for it! I didn't post the code originally because it is so long. (I seem to have a big issue with my Access application in that I cannot get the code to stop by adding breakpoints so whenever anythig goes wrong I have a huge problem trying to decode it - has anybody else experienced that as a problem?)

Public Sub ExcelExportDepartment(Department As Long)
   On Error Resume Next
    
    Dim xl As Object
    Dim xlWB As Object
    Dim ws As Object
    Dim db As DAO.Database
    Dim r As Object
    Dim s As Object
    Dim startRow1 As Long
    Dim startRow2 As Long
    Dim rsDetail As DAO.Recordset
    Dim rsTargetsDetail As DAO.Recordset
    
    Set rsDetail = Nothing
    Set rsTargetsDetail = Nothing
    
    Dim newName As String
    newName = BaseFolder(CurrentDb.Name) & "ESP Portfolio" & Department & ".xls"
    
    Set xl = CreateObject("Excel.Application")
    
    With xl.Application
        .UserControl = False
        .Visible = False
    End With
    
        If Not xlWB Is Nothing Then
            xlWB.Close False
            Set xlWB = Nothing
        End If
    
    If Dir(newName) <> "" Then Kill newName
    
    On Error GoTo 0
    
        If Department = 2 Then
                Call cmdOpenCSExcel
            Exit Sub
            End If
    If Department = 5 Then
    Set xlWB = xl.Workbooks.Add(BaseFolder(CurrentDb.Name) & "IT ESP Portfolio.xls")
    xlWB.Application.Calculation = xlManual
    Else
    Set xlWB = xl.Workbooks.Add(BaseFolder(CurrentDb.Name) & "ESP Portfolio.xls")
    xlWB.Application.Calculation = xlManual
        
 End If
    
    'Project Data
    Set db = CurrentDb
    Set ws = xlWB.Sheets("ProjectData")
    
    Set rsDetail = db.OpenRecordset("SELECT  * FROM qExcelMIData WHERE qExcelMIData.tblDepartment_ID = " & Department)
    
    If rsDetail.EOF Then 'No Records
        msgbox ("No Projects Found")
        Exit Sub
    End If
        
    Set r = ws.Range("Data_Row")
    startRow1 = r.Row
    
    While Not rsDetail.EOF
        r.Cells(1, 1) = Nz(rsDetail("ID_MAIN"))
        r.Cells(1, 2) = Nz(rsDetail("Department"))
        r.Cells(1, 3) = Nz(rsDetail("Head_Project_ID"))
        r.Cells(1, 4) = Nz(rsDetail("Project_Name"))
        r.Cells(1, 5) = Nz(rsDetail("Project_Description"))
        r.Cells(1, 6) = Nz(rsDetail("tblCostArea_ID"))
        r.Cells(1, 7) = Nz(rsDetail("tblInitiativeType_ID"))
        r.Cells(1, 8) = Nz(rsDetail("In_Year_Opportunity"))
        r.Cells(1, 9) = Nz(rsDetail("Annualised_Opportunity"))
        r.Cells(1, 10) = Nz(rsDetail("CurrentPhase"))
        r.Cells(1, 11) = Nz(rsDetail("tblProgressStatus_ID"))
        r.Cells(1, 12) = Nz(rsDetail("Latest_Update"))
        r.Cells(1, 13) = Nz(rsDetail("Date_Updated"))
        r.Cells(1, 14) = Nz(rsDetail("Project Leader"))
        r.Cells(1, 15) = Nz(rsDetail("Portfolio"))
        r.Cells(1, 16) = Nz(rsDetail("tblPTPInvolvement_ID"))
        r.Cells(1, 17) = Nz(rsDetail("Benefit_Form_Ref"))
        r.Cells(1, 18) = Nz(rsDetail("Define_Finish"))
        r.Cells(1, 19) = Nz(rsDetail("Analyse_Finish"))
        r.Cells(1, 20) = Nz(rsDetail("Improve_Finish"))
        r.Cells(1, 21) = Nz(rsDetail("Control_Finish"))
        r.Cells(1, 22) = Nz(rsDetail("Portfolio_Costs_Study"))
        r.Cells(1, 23) = Nz(rsDetail("Portfolio_Costs_Delivery"))
        r.Cells(1, 24) = Nz(rsDetail("Spent_Costs_Study"))
        r.Cells(1, 25) = Nz(rsDetail("Spent_Costs_Delivery"))
        Set r = ws.Range(r.Offset(1).Address)
        rsDetail.MoveNext
    Wend
    
    
    'Targets Data
    If Department = 5 Then
                Set db = CurrentDb
                Set ws = xlWB.Sheets("TargetsData")
                Set rsTargetsDetail = db.OpenRecordset("SELECT * FROM q2007ITTargets")
                Set s = ws.Range("Targets_Row")
                startRow2 = s.Row
                
                While Not rsTargetsDetail.EOF
                        s.Cells(1, 1) = Nz(rsTargetsDetail("Department"))
                        s.Cells(1, 2) = Nz(rsTargetsDetail("ID_DEPARTMENT"))
                        s.Cells(1, 3) = Nz(rsTargetsDetail("2007_In_Year_Target"))
                        s.Cells(1, 4) = Nz(rsTargetsDetail("2008_Annualised_Target"))
                        s.Cells(1, 5) = Nz(rsTargetsDetail("In_Year_Income_Target"))
                        s.Cells(1, 6) = Nz(rsTargetsDetail("Annualised_Income_Target"))
                    Set s = ws.Range(s.Offset(1).Address)
                    rsTargetsDetail.MoveNext
                Wend
    Else
                Set db = CurrentDb
                Set ws = xlWB.Sheets("TargetsData")
                Set rsTargetsDetail = db.OpenRecordset("SELECT * FROM q2007Targets WHERE q2007Targets.ID_DEPARTMENT =" & Department)
                Set s = ws.Range("Targets_Row")
                startRow2 = s.Row
                
                While Not rsTargetsDetail.EOF
                        s.Cells(1, 1) = Nz(rsTargetsDetail("Department"))
                        s.Cells(1, 2) = Nz(rsTargetsDetail("ID_DEPARTMENT"))
                        s.Cells(1, 3) = Nz(rsTargetsDetail("2007_In_Year_Target"))
                        s.Cells(1, 4) = Nz(rsTargetsDetail("2008_Annualised_Target"))
                        s.Cells(1, 5) = Nz(rsTargetsDetail("In_Year_Income_Target"))
                        s.Cells(1, 6) = Nz(rsTargetsDetail("Annualised_Income_Target"))
                    Set s = ws.Range(s.Offset(1).Address)
                    rsTargetsDetail.MoveNext
                Wend
    End If
    
    
    With xl.Application
        .Visible = True
        .Run ("UpdateTables")
        .Calculation = xlCalculationAutomatic
        .UserControl = True
        .Sheets("Instructions").Select
        .SetWarnings = True
        End With
             
End Sub

 

[RoryA]

do you have a reference set to the Excel object library?

 

[Norie]

Stuart

The first thing to do is to remove On Error Resume Next.

Then you should be able to debug the code when it errors.

It might also help if you told us what the code is meant to do.

I've not fully examined it but all it seems to be doing is transferring data from Access to Excel.

And it also looks as though you could possibly overcomplicating that.

 

[StuLux]

Rory - yes I've got the references to the Excel object library.
Norie - As you have observed the code is simply taking data from Access and putting it in Excel. I have some Excel workbooks that have an external data link to the Access database but my reason for wanting this code to send the data is that it uses a filter based on a field in Access before sending the data. I'd welcome any suggestions for better ways to do this - I am still very much a newbie at a lot of this stuff, particularly Access.
I'm going to continue looking at this on Monday as right now it's 4:15pm where I am and the office is closing down (gets earlier every Friday!).

 

[RoryA]

You should probably look at Excel's Range.CopyFromRecordset method - it will be a lot quicker. Also, if you have the reference set, you should use early binding and declare the Excel objects as specific types, not as Object.

 

[RoryA]

I think this line is your problem:

.SetWarnings = True

the Excel.Application object uses DisplayAlerts, not SetWarnings which is an Access thing.

 

[StuLux]

Rory

Many thanks for looking at my code - you're right the SetWarnings line appears to be the culprit.

I will look at the CopyFromRecordSet method as a way of sending the data between Access and Excel - any pointers on how I would filter the data i.e. whatI am trying to achieve is that (in Access) a user selects a department from a dropdown list and then (in Excel) a workbook opens with only data relating to that department.