Clearing Worksheet before export of filtered query data (1 Viewer)

MooTube

Registered User.
Local time
Yesterday, 22:05
Joined
Jul 2, 2015
Messages
31
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:

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
 

vbaInet

AWF VIP
Local time
Today, 05:05
Joined
Jan 22, 2010
Messages
26,374
Deleting the worksheet is quicker. Use Intellisense and Early binding to figure it out.

1. Add a reference to "Microsoft Excel".

2. Change your code to:
Code:
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    xlApp.WorkBooks.Open ("C:\Program Files (x86)\MCB\Excel\Label Export.xlsx")

3. Use Intellisense to figure out how to delete and add a worksheet.

After you've figured it out, change your code back to late binding.

Or just delete the entire file before running the code.
 

MooTube

Registered User.
Local time
Yesterday, 22:05
Joined
Jul 2, 2015
Messages
31
Thanks for the quick reply...

I like the idea of deleting the worksheet so that I can get it reopened, I am attempting to do this by using this code:

Code:
Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    
        With xlApp
            .Visible = True
            .WorkBooks.Open ("C:\Program Files (x86)\MCB\Excel\Label Export.xlsx")
            .Sheets("ExportQuery").Select
            .DisplayAlerts = False
            .ActiveSheet.Delete
            .DisplayAlerts = True
            .Range("A1").Select
        End With
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ExportQuery", strFile
But I am getting an error with the first line "User-Defined type not defined"

I am guessing this stems from your "Add a reference to Microsoft Excel" step. Sorry to be difficult but what did you mean by this? Am I missing something really obvious?

Thanks in advance :)

EDIT:

I changed the code to:

Code:
Private Sub Command67_Click()

    
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    
        With xlApp
            .Visible = True
            On Error Resume Next
            .WorkBooks.Open ("C:\Program Files (x86)\MCB\Excel\Label Export.xlsx")
            .Sheets("ExportQuery").Select
            .DisplayAlerts = False
            .ActiveSheet.Delete
            .Sheets("Sheet2").Select
            .ActiveSheet.Delete
            .DisplayAlerts.True
        End With
        
    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

and it runs now, but doesn't send the data over any more. Am I deleting the worksheet after the data is sent?

for some reason there were 2 linked sheets that had to be deleted but I'm not sure why. If anyone has any idea how to solve this I would be really grateful!
 
Last edited:

MooTube

Registered User.
Local time
Yesterday, 22:05
Joined
Jul 2, 2015
Messages
31
SOLVED

Instead of deleting sheets withing the workbook, I deleted the workbook and had it create a new one every time, this ensured there was no overlap of data and it could be exported straight away.

I also added a portion to open my mail merge document from word:

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

            Kill ("C:\Program Files (x86)\MCB\Excel\Label Export.xls")
        
        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.xls"
    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.xls"), True, False
        
    Set xlApp = Nothing
    
    Set objWord = CreateObject("Word.Application")
    
        With objWord
            .Visible = True
            .Documents.Open ("C:\Program Files (x86)\MCB\Merges\Label Merge.docx")
        End With

End Sub

Thanks for the help!
 

vbaInet

AWF VIP
Local time
Today, 05:05
Joined
Jan 22, 2010
Messages
26,374
Good job!

NB: Remember to include an error handler on the Kill statement.
 

Users who are viewing this thread

Top Bottom