Activeworkbook.worksheets error: object doesn't supoprt this method

[Rita777]

Hello,I am working in excel/access 2003. I have a macro running out of access in which I create an excel workbook (a copy of an existing template) and try to paste the results of several access queries into this workbook. I first try to check if a copy already exists or not. If it does I delete it and create a new copy of the template. Then I go through the queries I need to paste. The query parameters are dependent on the value of each worksheet's value in cell A1. I pass that value to the query and then paste the recordset. Now, I have two problems that I have been going crazy over.

The first problem is that the code gets through creating the workbook only the first time that I run it. After that, I manually need to go into excel and delete out the created copy because I cannot figure out how to close the workbook programatically before I can delete it. Using objExcel.Workbooks.Close(" path to workbook ") gives me an object required error (runtime 424 error). Can anyone please help me out with the proper way to close a workbook programatically? I've searched and searched and cannot for the life of me figure out where I am going wrong with this .Close thing.

My second issue- and a much more frsutrating one- comes up when the code reaches the loop that is supposed to loop through the workbook's sheets and paste the desired recordsets. Here, I get an 'object doesn't support this property or method' error when I try to use xlApp.Activeworkbook.worksheets(i). The issue is that I am not sure if in creating the workbook, I have activated it (can someone please clarify this point for me?) and if it is in fact activated, how can I actually use its Worksheets collection? If my code hasn't in fact activated anything, how can I do so? Do I need to set the created workbook as a variable (I am not sure how to do that with a path string)?

My code is below. I would appreciate any suggestions. Thank you in advance and I hope everyone has a very happy and safe Thanksgiving!

Option Compare Database
Sub boblarsontest()
   Dim dbs    As DAO.Database
    Dim rs     As DAO.Recordset
    Dim TaskString As String
    Dim xlApp  As Object
    Dim ws     As Variant
    Dim strSQL As String, contract As String
    Dim intRow As Integer
    Dim i      As Integer
    Dim countrecords As Long
    Set dbs = CurrentDb
    Dim x As Integer
    
strSQL = "Select * from Pay inner join Task on Pay.EMPLID= Task.EMPLID "
    Set xlApp = CreateObject("Excel.Application")
      Dim sPath As String
    
     'Test if file exists
With New FileSystemObject
    If .FileExists("H:\BUDGET Reporting\TEST\Test Report output.xls") Then

'this is where I get an 'Error 424 Object Required' :   
objExcel.Workbooks.Close ("H:\BUDGET Reporting\TEST\Test Report output.xls")
    .DeleteFile "H:\BUDGET Reporting\TEST\Test Report output.xls"
    End If
End With
FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"
 
 Debug.Print "workbook created"
 
xlApp.Workbooks.Open ("H:\BUDGET Reporting\TEST\Test Report output.xls")
x = xlApp.Activeworkbook.worksheets.Count

    For i = 1 To x
    Debug.Print "loop started"
    ws = xlApp.Activeworkbook.worksheets(i)
      TaskString = ws.range("A1").Value
    
        strSQL2 = strSQL & "where FTE.Task = " & Chr(34) & TaskString & Chr(34) & ";"
  
        Set rs = dbs.OpenRecordset(strSQL2)
        rs.MoveLast
    
        countrecords = rs.RecordCount
  
        rs.MoveFirst
        ws.range("A" & countrecords + 2 & ":A2").entirerow.insert
            ws.range("A3").CopyFromRecordset rs
        rs.Close
    Next
    xlApp.Visible = True
    xlApp.Activeworkbook.Save
    
    Set rs = Nothing
End Sub

Thank you!

 

[Rita777]

Thank you Xenou. We worked it out for this case, but your code is extremely helpful for other cases when I need to delete out explicitly. I will definitely be needing this for a couple other macros. Thank you!