troubleshooting Question

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

Avatar of limobill
limobill asked on
Microsoft AccessMicrosoft Excel
11 Comments1 Solution545 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros