Solved

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

Posted on 2011-09-08
11
484 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
Technology Partners: 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!

 
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

Technology Partners: 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

719 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