• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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

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

1 Solution
Rey Obrero (Capricorn1)Commented:
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

jmccloskAuthor Commented:
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!

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now