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 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
stDocName = "mac_Email_Output"
' 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"
' Close and Cleanup