I have a module in Access 2010 that exports the results of a select query to excel, opens the .xlsx file, then calls a Sub to distribute the data to multiple worksheets in the excel file and delete the master sheet. Everything works great, except upon deleting the master sheet at the end I receive a 'Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete.' alert. I want to bypass this, however when I add Application.DisplayAlerts = False to my Sub, I receive a Compile error, 'Method or data member not found' on the Excel Sub. I assume this has something to do with the fact that I'm running Access VBA and calling a sub with XL defined as the object, but I am not sure how to alter the code to compensate for this, or if that's possible. My present code is as follows:
<CODE>Function ExportTerminations()
'Code for exporting Query
DoCmd.OutputTo acOutputQuery, "Terms_In_Range", "Excel Workbook(*.xlsx)", _
"I:\HR\DATABASES\PES\PES Reporting\Safe Act\NMLS Responses\Termination Reports\Term Report.xlsx", _
False, "", 0, acExportQualityScreen
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "I:\HR\DATABASES\PES\PES Reporting\Safe Act\NMLS Responses\Termination Reports\Term Report.xlsx"
XL.Visible = True
With XL
Call procedure_below
End With
End Function
Excel called from Access Module above:
Sub procedure_below()
Dim WBO As Workbook
Dim ThisWS
Dim rngFilter As Range 'filter range
Dim rngUniques As Range 'Unique Range
Dim cell As Range
Dim counter As Integer
Dim rngResults As Range 'filter range
Set rngFilter = Range("J1", Range("J" & Rows.Count).End(xlUp))
Set rngResults = Range("A1", Range("L" & Rows.Count).End(xlUp))
Application.DisplayAlerts = False
With rngFilter
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUniques = Range("J2", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
ActiveSheet.ShowAllData
End With
For Each cell In rngUniques
Worksheets.Add After:=Worksheets(Worksheets.Count)
ThisWS = cell.Value
ActiveSheet.Name = ThisWS
counter = counter + 1
rngFilter.AutoFilter Field:=1, Criteria1:=cell.Value
rngFilter.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(ThisWS).Range("A1")
rngResults.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(ThisWS).Range("A1:L1")
Cells.Select
Selection.ColumnWidth = 40.86
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
Next cell
rngFilter.Parent.AutoFilterMode = False
Sheets("Terms_In_Range").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub
</CODE>
Can anyone tell me what I'm doing wrong?
Thanks in advance for any help!!
<CODE>Function ExportTerminations()
'Code for exporting Query
DoCmd.OutputTo acOutputQuery, "Terms_In_Range", "Excel Workbook(*.xlsx)", _
"I:\HR\DATABASES\PES\PES Reporting\Safe Act\NMLS Responses\Termination Reports\Term Report.xlsx", _
False, "", 0, acExportQualityScreen
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "I:\HR\DATABASES\PES\PES Reporting\Safe Act\NMLS Responses\Termination Reports\Term Report.xlsx"
XL.Visible = True
With XL
Call procedure_below
End With
End Function
Excel called from Access Module above:
Sub procedure_below()
Dim WBO As Workbook
Dim ThisWS
Dim rngFilter As Range 'filter range
Dim rngUniques As Range 'Unique Range
Dim cell As Range
Dim counter As Integer
Dim rngResults As Range 'filter range
Set rngFilter = Range("J1", Range("J" & Rows.Count).End(xlUp))
Set rngResults = Range("A1", Range("L" & Rows.Count).End(xlUp))
Application.DisplayAlerts = False
With rngFilter
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUniques = Range("J2", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
ActiveSheet.ShowAllData
End With
For Each cell In rngUniques
Worksheets.Add After:=Worksheets(Worksheets.Count)
ThisWS = cell.Value
ActiveSheet.Name = ThisWS
counter = counter + 1
rngFilter.AutoFilter Field:=1, Criteria1:=cell.Value
rngFilter.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(ThisWS).Range("A1")
rngResults.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(ThisWS).Range("A1:L1")
Cells.Select
Selection.ColumnWidth = 40.86
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
Next cell
rngFilter.Parent.AutoFilterMode = False
Sheets("Terms_In_Range").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub
</CODE>
Can anyone tell me what I'm doing wrong?
Thanks in advance for any help!!