?
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
Medium Priority
?
2,586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 2000 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 34

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 34

Expert Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

718 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