How to resolve the error 'Runtime error '1004': Method 'Worksheets' of object '_Global' failed in an Access 2003 application?

How to resolve the error 'Runtime error '1004': Method 'Worksheets' of object '_Global' failed in an Access 2003 application?

The statement that caused the error is:

xlWB.Worksheets.Add After:=Worksheets(Worksheets.count)

The following is the method that led to the error:

Private Sub ExportToExcels(filename As String)
Dim str_sql As String
Dim cn As ADODB.Connection
Dim xl As Excel.Application
Dim xlWB As Excel.Workbook
Dim sht As Excel.Worksheet, rng As Excel.Range
Dim db As DAO.Database, rs As ADODB.Recordset
Dim recordtotal As Long
Dim SheetNum As Long
Dim dest As Range
Dim Counter As Long
Dim Source As Workbook
Dim col As Long
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set db = CurrentDb

recordtotal = DCount("ProdID", "tblRemedInternal")
Set xl = CreateObject("Excel.Application")

Set xlWB = xl.Workbooks.Add
xlWB.SaveAs filename
Set xlWB = xl.Workbooks.Open(filename)

xl.Visible = True
SheetNum = 1
Do While recordtotal > 0
    rs.Open "Select top 60000 * from tblRemedInternal", cn, 2, 2
    If rs.EOF Then Exit Sub
    rs.MoveFirst
        Set sht = Nothing
        On Error Resume Next
        Set sht = xlWB.Worksheets("Sheet" & SheetNum)
        On Error GoTo 0
       
        If sht Is Nothing Then
        xlWB.Worksheets.Add After:=Worksheets(Worksheets.count) <---- Error Here
            Set sht = ActiveSheet
            sht.Name = "Sheet" & SheetNum
        End If
        For col = 0 To rs.Fields.count - 1
            sht.Cells(1, col + 1).Value = rs.Fields(col).Name
        Next
        sht.Range("A2").CopyFromRecordset rs
        SheetNum = SheetNum + 1
        str_sql = "delete from tblRemedInternal where ProdID in(Select top 60000 ProdID from tblRemedInternal)"
        DoCmd.RunSQL (str_sql)
    rs.Close
    recordtotal = DCount("ProdID", "tblRemedInternal")
Loop

xlWB.Close (True)
xl.Quit
Set xl = Nothing

End Sub
zimmer9Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this.
With xlWB
    .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
    Set sht = .Worksheets(.Worksheets.Count)
    sht.Name = "Sheet" & SheetNum
End With

Open in new window

0
 
zimmer9Author Commented:
Run-time error '1004'
Cannot rename a sheet to the same name as another sheet, a referenced object library
or a worksheet referenced by Visual Basic
------------------------------------------------------

Do While recordtotal > 0
    rs.Open "Select top 60000 * from tblRemedInternal", cn, 2, 2
    If rs.EOF Then Exit Sub
    rs.MoveFirst
        Set sht = Nothing
        On Error Resume Next
        Set sht = xlWB.Worksheets("Sheet" & SheetNum)
        On Error GoTo 0
        '----------------------  New code starts here
        With xlWB
            .Worksheets.Add After:=.Worksheets(.Worksheets.count)
            Set sht = .Worksheets(.Worksheets.count)  
            sht.Name = "Sheet" & SheetNum   <-----  Compiler stops here
        End With
        '----------------------  New code ends here

        'If sht Is Nothing Then
        '    xlWB.Worksheets.Add After:=Worksheets(Worksheets.count)
        '    Set sht = ActiveSheet
        '    sht.Name = "Sheet" & SheetNum
        'End If
       
        For col = 0 To rs.Fields.count - 1
            sht.Cells(1, col + 1).Value = rs.Fields(col).Name
        Next
        sht.Range("A2").CopyFromRecordset rs
        SheetNum = SheetNum + 1
        str_sql = "delete from tblRemedInternal where ProdID in(Select top 60000 ProdID from tblRemedInternal)"
        DoCmd.RunSQL (str_sql)
    rs.Close
    recordtotal = DCount("ProdID", "tblRemedInternal")
Loop
0
 
NorieVBA ExpertCommented:
Why did you put the code I posted outside the If?
0
 
zimmer9Author Commented:
My bad.
0
 
NorieVBA ExpertCommented:
No, my bad - I should have posted the code with the If End If.:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.