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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

16 Experts available now in Live!

Get 1:1 Help Now