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]

Hi,

You reference an open workbook by it's name only, not the full path:

xlApp.Workbooks("[COLOR="Blue"]Book1.xls[/COLOR]").Close

Often you use the full path to open a workbook that is not open, and after that you refer to it by name. However, I'm not keen on this attempt to close and kill the file. If it's already open it won't work - you won't be able to delete a file that is in use. I like to check for all such potential problems at the beginning of the routine and exit if there is an issue. This should be enough - just try to kill the file, and if you can't then you know it's in use so abort:

On Error Resume Next
Kill "H:\BUDGET Reporting\TEST\Test Report output.xls"
If CreateObject("Scripting.FileSystemObject").FileExists("H:\BUDGET Reporting\TEST\Test Report output.xls") Then
    Exit Sub
End If
On Error GoTo 0

 

[xenou]

This code is wrong because you need a set keyword in the syntax:

ws = xlApp.Activeworkbook.worksheets(i)

Should be:

set ws = xlApp.Activeworkbook.worksheets(i)

 

[xenou]

Finally, it will be easier to use a workbook reference variable just like you do with worksheets:

[COLOR="Blue"]Dim wb as Workbook[/COLOR]
[COLOR="Blue"]Set wb[/COLOR] = xlApp.Workbooks.Open ("H:\BUDGET Reporting\TEST\Test Report output.xls")
x = [COLOR="Blue"]wb[/COLOR].worksheets.Count

    For i = 1 To x
    Debug.Print "loop started"
    [COLOR="Red"]Set[/COLOR] ws = [COLOR="Blue"]wb[/COLOR].worksheets(i)
      TaskString = ws.range("A1").Value

When you are finished to close the workbook (no danger about identifying it anymore since you have a reference variable):

[COLOR="Blue"]wb[/COLOR].Close SaveChanges:=True

I'd recommend you post your alterations for a second look from some of our expert eyes here, and let us know how it's going.

ξ

 

[Rita777]

Hi Xenou, thank you so much for your reply! I have incorporated your suggestions, but when I try to compile, I get a compile error 'User-defined type not defined' at the line:
Dim wb As Workbook

Does this have to do with the reference libraries I may not have set or something else that I am missing? Please suggest. Thank you! The modified code is below:

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")


On Error Resume Next
Kill "H:\BUDGET Reporting\TEST\Test Report output.xls"
If CreateObject("Scripting.FileSystemObject").FileExists("H:\BUDGET Reporting\TEST\Test Report output.xls") Then
Exit Sub
End If
On Error GoTo 0

FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"

Debug.Print "workbook created"

Dim wb As Workbook
Set wb = xlApp.Workbooks.Open("Test Report output.xls")x = wb.worksheets.Count

strSQL = "Select * from Pay inner join Task 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 done"
strSQL2 = strSQL & "where FTE.Task = " & 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 inserrted"
ws.range("A3").CopyFromRecordset rs
rs.Close
Next
xlApp.Visible = True


Set rs = Nothing
wb.Close SaveChanges:=True
End Sub

 

[xenou]

Ah sorry.

Try instead

Dim wb as Object

 

[xenou]

Dim wb As Workbook
Set wb = xlApp.Workbooks.Open("Test Report output.xls")x = wb.worksheets.Count

strSQL = "Select * from Pay inner join Task on Pay.EMPLID= Task.EMPLID "

For i = 1 To x
Debug.Print "loop started"
Set ws = [COLOR="Red"]xlApp.Activeworkbook.worksheets(i)[/COLOR]

Once you create a workbook variable be sure to use it:

Set ws = wb.worksheets(i)

Post back again with your edits and the complete code - and success or failure running it (hopefully the former).

 

[Rita777]

Thank you Xenou! I actually tried that after posting my reply. It works and the code compiles. However, I am still having the persistent problem of not being able to run the code more than once. I ran it once and it created the workbook. It didn't paste any results into the copy (I assume there is something wrong with my query). So I tried to run it again. However, this time, it just exits the sub without creating the workbook. And I cannot close the workbook manually. When I go into excel and try to delete it, it gives me a message that the workbook is in use by an application. That means that the code didn't actually kill it. This one is tougher than I thought it would be....

 

[Norie]

What's all the file stuff at the start actually meant to do?

Whatever it is I would suggest removing On Error Resume Next.

Then if there are any errors they won't just be skipped over.

Part of that code seems to be for deleting a workbook but it doesn't check if the workbook exists.

I think it might be an idea to check for existence first.

There's another couple of things that don't seem right, for example there's no path when you try and open the workbook.

 

[Rita777]

Hello, thank you both for you replies! I have gotten it to work... sort of. I get through the code of creating the workbook and pasting and all that, but it doesn't actually paste anything. I think, though, that that's a problem with my sql query syntax. I will try to figure out why it isn't pasting anything and post back with the correct code. In the meantime, below is the code that I got to work semi-successfully (I left all the old exist-check stuff that didn't work as comments). Here, the very useful function FileOrDirExists() can be found here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=559

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")
   
   
   'On Error Resume Next
'Kill "H:\BUDGET Reporting\TEST\Test Report output.xls"
'If CreateObject("Scripting.FileSystemObject").FileExists("H:\BUDGET Reporting\TEST\Test Report output.xls") Then
'Dim wb As Object
'Set wb = xlApp.Workbooks.Open("H:\BUDGET Reporting\TEST\Test Report output.xls")
'wb.Close
'Debug.Print "wb closed"
'wb.Delete
'Debug.Print "wb deleted"
    'Exit Sub
'End If
'On Error GoTo 0
Dim copypath As String
copypath = "H:\BUDGET Reporting\TEST\Test Report output.xls"
If FileOrDirExists(copypath) Then
'Dim wb As Object
'Set wb = xlApp.Workbooks.Open("H:\BUDGET Reporting\TEST\Test Report output.xls")
'wb.Close
'Debug.Print "wb closed"
'wb.Delete
'Debug.Print "wb deleted"
Else
FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"
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
 
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 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 inserrted"
        ws.range("A3").CopyFromRecordset rs
        Debug.Print "pasted"
        rs.Close
    Next
    xlApp.Visible = True
    
   Set rs = Nothing
   wb.Close SaveChanges:=True
End Sub