I am currently working on a form that exports a query that changes after a user set filter is applied, and am able to get the filter to apply and the query to export. The problem I am having is that the worksheet that the query is copied into retains all previous data, and if the earlier query export included more records, they remain as they were, is there any way of getting them to be blank.
E.G I want to export my query onto a worksheet that has current data, need to delete current data or delete worksheet so that only the selected data is shown.
my code currently is:
E.G I want to export my query onto a worksheet that has current data, need to delete current data or delete worksheet so that only the selected data is shown.
my code currently is:
Code:
Private Sub Command67_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT NomT.shkFirstName, NomT.shkSurName, NomT.shkCompanyName, NomT.shkAdd1, NomT.shkAdd2, NomT.shkPostCode, NomT.shkRegion, NomT.shkCountry, NomT.shkAdd3" & " FROM NomT" & vbCrLf
With Me.FilterSub.Form
If .FilterOn Then
strWhere = "WHERE " & .Filter & vbCrLf
End If
End With
CurrentDb.QueryDefs("ExportQuery").SQL = strcStub & strWhere
strFile = "C:\Program Files (x86)\MCB\Excel\Label Export.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ExportQuery", strFile
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.WorkBooks.Open ("C:\Program Files (x86)\MCB\Excel\Label Export.xlsx")
End Sub