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,444 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

828 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