Using Access 2003 to create multi-tabbed Excel 2003 workbook and rename Tabs

Posted on 2011-04-28
Last Modified: 2012-05-11
I am trying to automate the process in Microsoft Access 2003 of creating a single Excel 2003 workbook to be e-mailed out to management which includes five Tabs, each Tab being a unique query from Access.  The queries have crazy names and are not easily understandable.  So, after the VBA code creates the workbook it then goes through and renames the Tabs for each report to be simpler English.  I have attached the code for this, some I wrote and some I got from looking up Google searches on coding specific tasks.  

My problem is that, on the first try of running this code, it works perfectly!  But the second time I run it, I get the following message, "Run-time error '1004': Method 'Sheets' of object '_Global' failed.

If I simply click the "end" option to this message and run the code again, it works perfectly.  But, the fourth time I get the same error again!  It seems that, every other time I run it, it errors.

I have posted the code with this question.  The first line simply deletes the old spreadsheet before creating a new one.  The next five lines after that run a macro to transfer the five queries to the newly created workbook using the TransferSpreadsheet command.

If anyone can identify why this error keeps popping up every other time I run it, I would greatly appreciate you showing me the error of my ways!  Thank you!
Option Explicit
Option Compare Database

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Private Sub Create_and_View_Attachment_Click()

Kill "C:\First Alert\System Folder\DRCycle1Info.xls"

Dim stDocName As String

    DoCmd.SetWarnings False
        stDocName = "mac_Email_Output"
        DoCmd.RunMacro stDocName
    DoCmd.SetWarnings True
 ' Use this to make sure your variables are defined
 ' One way to be able to use these objects throughout the Module is to
 ' Declare them here, and not in a Sub

 Dim strFile As String
 strFile = "C:\First Alert\System Folder\DRCycle1Info.xls"

 ' Opens Excel and makes it Visible
 Set objExcel = New Excel.Application
 objExcel.Visible = True
 ' Opens up a Workbook
 Set xlWB = objExcel.Workbooks.Open(strFile)
 ' Sets the Workseet to the last active sheet - Better to use the commented
 ' version and use the name of the sheet.
 Set xlWS = xlWB.ActiveSheet
 ' Set xlWS = xlWB("Sheet1")

 With xlWS ' You are now working with the Named file and the named worksheet
 ' Your Excel code begins here
 Sheets("2_qry2_Stores_On_Cur_Sched_With").Name = "StoreWithOrders"
 Sheets("4_qry_Stores_On_Cur_Sched_With_").Name = "StoreWithoutOrders"
 Sheets("qry_local_TWOPENORD1_Adjusted").Name = "SSAdjustOrderQty"
 Sheets("qry_local_TWOPNORDRX_Adjusted").Name = "RXAdjustOrderQty"
 Sheets("qry_Union_All_Item_Exceptions").Name = "ItemExceptions"

 End With

 ' Close and Cleanup

End Sub

Open in new window

Question by:jmcclosk
    LVL 119

    Accepted Solution

    try this revisions

    ' Opens Excel and makes it Visible
     Set objExcel = New Excel.Application
     	objExcel.Visible = True
     With objExcel 
     ' Your Excel code begins here
     .WorkSheets("2_qry2_Stores_On_Cur_Sched_With").Name = "StoreWithOrders"
     .WorkSheets("4_qry_Stores_On_Cur_Sched_With_").Name = "StoreWithoutOrders"
     .WorkSheets("qry_local_TWOPENORD1_Adjusted").Name = "SSAdjustOrderQty"
     .WorkSheets("qry_local_TWOPNORDRX_Adjusted").Name = "RXAdjustOrderQty"
     .WorkSheets("qry_Union_All_Item_Exceptions").Name = "ItemExceptions"
     End With
     ' Close and Cleanup

    Open in new window


    Author Closing Comment

    Perfect!  No more errors!  And, it looks simpler now without the need for the variables 'xlWB' & 'xlWS'.  Thank you for the help and the amazingly quick response!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now