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!

 

[xenou]

There is a typo:

"Select * from Pay inner join [COLOR="Red"]Tasks[/COLOR] on Pay.EMPLID= [COLOR="Red"]Task[/COLOR].EMPLID "

 

[Rita777]

Hello, thank you for your replies! Still working on it. Not getting the workbook to delete. I used a very handy function from http://www.vbaexpress.com/kb/getarticle.php?kb_id=559 to check if a workbook exists or not. I have gotten to the point where I can now check if the workbook exists, if it doesn't then create it and if it does, then open it. This is not what I want because I still cannot close and delete the existing copy of the Report output workbook, so I can create a brand new clean copy of the template.
When I try to set the wb as the workbook in the If part of the If statement and do wb.Delete I get an 'error 430: Class does not support automation or does not support expected interfaces' so I assume I am not deleting the workbook properly. How can I close and delete it programatically?

Currently, I am working with the situation of using the existing copy of the workbook (which exists because I ran the code already and the create part works, but the delete part doesn't, and every time I try to delete the workbook manually in excel, I get the message saying that it is in use and cannot be deleted). So anyway, I am bearing with this situation until I find how to get it to delete programatically. But now, once I get to the loop part of the code, I get a 'join syntax error' error after line

Set rs = dbs.OpenRecordset(strSQL2)

even though the taskstring prints fine in the immediate window. Please help! Thank you in advance!!

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
    Set xlApp = CreateObject("Excel.Application")
   
    Dim copypath As String
    copypath = "H:\BUDGET Reporting\TEST\Test Report output.xls"
If FileOrDirExists(copypath) Then
Debug.Print "wb exists"
Else
    On Error Resume Next
    FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"
   
End If
    Dim wb As Object
    Set wb = xlApp.Workbooks.Open("H:\BUDGET Reporting\TEST\Test Report output.xls")
    x = wb.worksheets.Count
strSQL = "Select * from Pay inner join Tasks on Pay.EMPLID= Task.EMPLID "
   
   For i = 1 To x
    Debug.Print "loop started"
   Set ws = xlApp.Activeworkbook.worksheets(i)
    Debug.Print "worksheet set"
        TaskString = ws.range("A1").Value
         Debug.Print TaskString
    Debug.Print "taskstring done"
        strSQL2 = strSQL & "where FTE.Tasks like " & Chr(34) & TaskString & Chr(34) & ";"
    Debug.Print "sql string set"
        Set rs = dbs.OpenRecordset(strSQL2)
        rs.MoveLast
    Debug.Print "move last"
        countrecords = rs.RecordCount
    Debug.Print "recordcount done"
        rs.MoveFirst
        ws.range("A" & countrecords + 2 & ":A2").entirerow.insert
    Debug.Print "rows inserted"
        ws.range("A3").CopyFromRecordset rs
        Debug.Print "pasted"
        rs.Close
    Next
    xlApp.Visible = True
    
   Set rs = Nothing
   wb.Close SaveChanges:=True
End Sub

 

[Rita777]

Hello Xenou, thank you for catching the typo. I have just posted code that I've been working on which provides semi-satisfactory results (though doesn't close or delete the existing copy) and I just fixed the typo that you caught and tried to run it, but now instead of a syntax error I get the error: "too few parameters, 2 expected." My edited code is below. Thank you for taking the time to help me!

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
    Set xlApp = CreateObject("Excel.Application")
   
    Dim copypath As String
    copypath = "H:\WTC BUDGET Reporting\WTC TEST\Test Report output.xls"
If FileOrDirExists(copypath) Then
Debug.Print "wb exists"
Else
    On Error Resume Next
    FileCopy Source:="H:\WTC BUDGET Reporting\WTC TEST\test report template.xls", Destination:="H:\WTC BUDGET Reporting\WTC TEST\Test Report output.xls"
   
End If
    Dim wb As Object
    Set wb = xlApp.Workbooks.Open("H:\WTC BUDGET Reporting\WTC TEST\Test Report output.xls")
    x = wb.worksheets.Count
strSQL = "Select * from Pay inner join Tasks on Pay.EMPLID= Tasks.EMPLID "
   
   For i = 1 To x
    Debug.Print "loop started"
   Set ws = xlApp.Activeworkbook.worksheets(i)
    Debug.Print "worksheet set"
        TaskString = ws.range("A1").Value
         Debug.Print TaskString
    Debug.Print "taskstring done"
        strSQL2 = strSQL & "where FTE.Tasks like " & Chr(34) & TaskString & Chr(34) & ";"
    Debug.Print "sql string set"
        Set rs = dbs.OpenRecordset(strSQL2)
        rs.MoveLast
    Debug.Print "move last"
        countrecords = rs.RecordCount
    Debug.Print "recordcount done"
        rs.MoveFirst
        ws.range("A" & countrecords + 2 & ":A2").entirerow.insert
    Debug.Print "rows inserted"
        ws.range("A3").CopyFromRecordset rs
        Debug.Print "pasted"
        rs.Close
    Next
    xlApp.Visible = True

 

[Norie]

Don't use ActiveWorkbook, you've got a reference to the workbook you've opened so use it.

Set ws = wb.Worksheets(i)

As for the query, shouldn't FTE.Tasks be Tasks.FTE assuming Tasks is the table and FTE is the field.

PS There's a VBA function that's kind of built-in to check if files/paths exist, Dir.

 

[Norie]

What files are invovled here?

You start with a template file presumably and want to create the 'output' file using that as a template.

What's next?

What file(s) should be deleted/closed?

 

[Rita777]

Xenou, you are absolutely right! It wasn't the Tasks table that you mentioned, but a different table- Pay- that had the problem with the EMPLID field name. Thank you so much! Now I feel silly for not catching it. I will be more careful in the future. Thanks again!

However, my other question is still outstading: how can I programatically close and delete the existing copy of a workbook? Please provide a resource for this if you have one. I searched just about everything there is, but somehow I am still missing something.

Thanks again and my working code is below, for anyone else looking to this string for guidance:

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
    Set xlApp = CreateObject("Excel.Application")
   
    Dim copypath As String
    copypath = "H:\BUDGET Reporting\TEST\Test Report output.xls"
 
If FileOrDirExists(copypath) Then
Debug.Print "wb exists"
Else
    On Error Resume Next
    FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"
   
End If
    Dim wb As Object
    Set wb = xlApp.Workbooks.Open("H:\BUDGET Reporting\TEST\Test Report output.xls")
    x = wb.worksheets.Count
strSQL = "Select * from Pay inner join FTE on Pay.EMPLID= FTE.EMPLID "
   
   For i = 1 To x
    Debug.Print "loop started"
   Set ws = wb.worksheets(i)
    Debug.Print "worksheet set"
        TaskString = ws.range("A1").Value
         Debug.Print TaskString
    Debug.Print "taskstring done"
        strSQL2 = strSQL & "where FTE.Task like " & Chr(34) & TaskString & Chr(34) & ";"
    Debug.Print "sql string set"
        Set rs = dbs.OpenRecordset(strSQL2)
        rs.MoveLast
    Debug.Print "move last"
        countrecords = rs.RecordCount
    Debug.Print "recordcount done"
        rs.MoveFirst
        ws.range("A" & countrecords + 2 & ":A2").entirerow.insert
    Debug.Print "rows inserted"
        ws.range("A3").CopyFromRecordset rs
        Debug.Print "pasted"
        rs.Close
    Next

 

[Rita777]

Hi Norie, thank you for posting again. The files involved are the 'test report template', which is the master file that I want to make a clean copy of every time I run this code. For the sake of being thorough, I want to close and delete the copy (called Test Report output) if one already exists and is open. Right now, the only way I got the code to work is:
I check for existence of the copy
if exists, I get out of the if and open the existing copy and start pasting in that one
if it doesn't exist, I create one

How can I make sure the old copy is deleted from this folder every time before a clean one is created?
Thank you!!

What files are invovled here?

You start with a template file presumably and want to create the 'output' file using that as a template.

What's next?

What file(s) should be deleted/closed?

 

[Norie]

Why not rename the existing file(s) and create a new one every time?

Or just create a new one every time and give it a unique name, eg add the date it was created to the file name.

Or you could use a 'real' template file so that every time you open it a new file is automatically created from it and the original is unchanged.

Or you could just open the template file and in Excel use SaveCopyAs to create a new copy.

That's all I can think of off the top of my head.

 

[Rita777]

Norie, thank you for the brilliiant idea! So simple, and gets the job done! I simply changed the last line to

wb.Close SaveAs

and now I'll be able to save different copies every time. I don't know why I was so anal about saving a clean copy under the same name every time. Thanks again for all your help!

 

[xenou]

A common reason for not being able to delete a file is that it is in use. If possible, just arrange it so the file is never in use. Otherwise, you will have to probably accept that you can't run the code when someone is using the file.

You can push the "delete" stuff out to a little function that returns true or false depending on if it succeeds:

Sub Foo()
Dim s As String
Dim x As Boolean

    s = "C:\SomeFolder\SomeFile.xls"

    x = DeleteMyWorkbook(s)
    If not x Then
        '//Workbook not deleted - must be in use - abort procedure
        Exit Sub
    End If

End Sub

'------------------------------------------------------------------

Private Function DeleteMyWorkbook(ByVal sPath As String) As Boolean
    
    On Error Resume Next
    Kill sPath
    If Err Then
        DeleteMyWorkbook = False
    Else
        DeleteMyWorkbook = True
    End If

End Function

EDIT: Never mind - I see you and Norie have it worked out. Excellent.