?
Solved

Run-Time Error 1004 creating multiple Worksheets in an Excel Workbook from MS Access

Posted on 2011-09-08
11
Medium Priority
?
490 Views
Last Modified: 2012-05-12
I'm am trying to right some code that will take an access table of PO Numbers and place each PO on a seperate tab in Excel.  I'm attaching the code that works 50% of the time.  I run it, the spreadsheet with multiple tabs is created.  If I close Excel and run it again I get the error method "sheets' of object'_global' failed # 1004.  The of code that fails is: Sheets.Add after:=Sheets(Sheets.Count).  If I clear the error and rerun it runs fine.  I'm stumped.  Code is below.  
Private Sub Command_Create_Report_Click()
On Error GoTo Err_Command_Create_Report_Click
Dim db As Database
Dim rs As Recordset
Dim rstble As Recordset
Dim i As Integer
Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook
Dim wksht As String
Dim strsql As String
strsql = "SELECT tbl_DB_POs.PO " & vbCrLf & _
"FROM tbl_DB_Report_Result INNER JOIN tbl_DB_POs ON tbl_DB_Report_Result.[PO Number] = tbl_DB_POs.PO " & vbCrLf & _
"GROUP BY tbl_DB_POs.PO;"
Set db = CurrentDb()
xl.Visible = True
xl.DisplayAlerts = False
Set wkbk = xl.Workbooks.Add
wkbk.SaveAs Filename:="C:\Users\wwalch\Documents\Old PC\Livery Database\Report.xls"
         With wkbk
      .Sheets("sheet2").Delete
      .Sheets("sheet3").Delete
       Set rs = db.OpenRecordset(strsql)
rs.MoveFirst
Do Until rs.EOF
wksht = rs.Fields("PO")
.Sheets.Add after:=Sheets(Sheets.Count)
.Sheets(Sheets.Count).Name = wksht
.Sheets(wksht).Range("B2").Cells.value = "CAR SERVICE BI-MONTHLY BILL"
rs.MoveNext
Loop
Exit Sub
End With
wkbk.Save
rs.Close: Set rs = Nothing
Exit Sub
Err_Command_Create_Report_Click:
MsgBox Err.Number & Err.Description
End Sub

Open in new window

0
Comment
Question by:limobill
[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
  • 4
  • 4
  • 3
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36505674
When copying or creating a worksheet in a workbook using VBA such as when using a template to generate multiple worksheets in the same workbook or in another workbook, an error can occur after a certain number of copies. The problem is caused when one or more names are defined on the worksheet. The error is displayed as "1004: Copy Method of Worksheet Class failed." This is a known problem in all versions of Excel up to and including Excel 2003.

To circumvent the problem save the destination workbook periodically during the process. For example, if creating 100 new worksheets from a template and the error occurs around the 50th worksheet, save the destination workbook every 25 new worksheets as illustrated with the example code below.

   Dim Counter As Long
   Do
      ThisWorkbook.Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
      Counter = Counter + 1
      If Counter > 25 Then
         ThisWorkbook.Save
         Counter = 0
      End If
   Loop

Kevin
0
 
LVL 34

Expert Comment

by:Norie
ID: 36505710
I think you are missing a couple of dot qualifiers.
.Sheets.Add after:=Sheets(.Sheets.Count).
Sheets(.Sheets.Count).Name = wksht

Open in new window

0
 
LVL 34

Expert Comment

by:Norie
ID: 36505734
Actually I just mucked them up to when I copied.
.Sheets.Add after:=Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = wksht

Open in new window

Can't seem to highlight them - there both before Sheets.Count.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36505740
I think imnorie is correct. If you do not qualify the objects Excel will make assumptions and, when you "clear the error" you are probably changing the environment (activating certain workbooks) that then allow the assumptions to work.

Kevin
0
 

Author Comment

by:limobill
ID: 36505851
I'm adding under 10 sheets so after Do until... I added wkbk.save.  It still runs once just fine.  If I close the spreadsheed and excel and rerun it - I get the error.  I've gone into the folder and deleted the actual workbook and it still fails but when I clear the error it runs again.  

I also tried adding the . qualifiers.  no change.  

Thanks.  
0
 
LVL 34

Expert Comment

by:Norie
ID: 36518941
How exactly did you add the qualifiers?
0
 

Author Comment

by:limobill
ID: 36525085
.Sheets.Add after:=Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = wksht

Still get the error every other time I run it.  
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36551512
Change:

.Sheets.Add after:=Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = wksht

to:

.Sheets.Add after:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = wksht

Kevin
0
 
LVL 34

Expert Comment

by:Norie
ID: 36551634
Oops, look like it was me that might have mislaid a dot qualifier.
0
 

Accepted Solution

by:
limobill earned 0 total points
ID: 36561900
All I stepped away from this for a while.  What seemed to finally work was to break out the creation of the workbook and adding the tabs into seperate functions.  I also saved and closed both the workbook and the application and the end of each and set the objects to nothing.  Code runs perfectly now and never hangs.    
0
 

Author Closing Comment

by:limobill
ID: 36591680
None of the solutions given worked.  I kept trying different things until I got it to work. Not sure my solution is the best hence the B.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

765 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