How to export table data based on criteria to a new excel workbook wothout saving it

[chris88]

Hi. I need export records form a table to excel. Everything ive come accross so far requires the excel file to be saved as part of the code and the docmd.transferspreadsheet method seems to be the most common way to export to excel but i dont want the excel file saved anywhere. Just need it as a new workbook that is then up to the end user to save if required.

There is only 1 criteria i need to identify what records to export and that being where column a = form text box value.

I want this code to run on button click on the same form the text box criteria resides.

Any help would be appreciated as im now at a loss. Thanks

 

[static]

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set ws = xl.Workbooks.Add.Sheets(1)
With ws.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=MS Access Database;DBQ=" & CurrentProject.FullName & ";", Destination:=ws.Range("$A$1")).QueryTable
    .CommandText = "SELECT *  FROM Table1 WHERE somefield='" & whatever & "'"
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

 

[chris88]

Thanks static.

Looks pretty straight forward from your code so will test it today.

Thanks