Solved

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

Posted on 2011-09-08
11
468 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
  • 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 33

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 33

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

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 33

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

707 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

17 Experts available now in Live!

Get 1:1 Help Now