Solved

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

Posted on 2013-11-03
5
2,364 Views
Last Modified: 2013-11-03
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
0
Comment
Question by:zimmer9
  • 3
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39620402
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
 

Author Comment

by:zimmer9
ID: 39620429
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
 
LVL 33

Expert Comment

by:Norie
ID: 39620469
Why did you put the code I posted outside the If?
0
 

Author Comment

by:zimmer9
ID: 39620500
My bad.
0
 
LVL 33

Expert Comment

by:Norie
ID: 39620527
No, my bad - I should have posted the code with the If End If.:)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now