Sleeper: Exporting query to Excel file saved as attachment in my Access database

[sal87]

Hallo Everybodey,

I want a vba script that export my query result to an excel file which it is saved in my access database as attachment.
My code is working if the excel template is on the hard drive but i need to be saved as attachment.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strPathFileName As String, strWorksheetName As String
Dim strRecordsetDataSource As String
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
Set strFileName = CurrentDb.OpenRecordset("SELECT attachement.FileName FROM tbl_Attachement WHERE Id=1", dbOpenDynaset, dbseechanges)
strRecordsetDataSource = "Select * from mytable"
blnHeaderRow = True
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
' Create a new EXCEL workbook
Set xlw = xlx.Workbooks.Open("C:\Temp\Report.xlsx")
Set xls = xlw.Worksheets("help_tab")
'xls.Name = Trim(Left("1.1", 31))
Set xlc = xls.Range("B2")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
      xlc.CopyFromRecordset rst
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

[Gasman]

Crossposted first at https://www.accessforums.net/showthread.php?t=88058

[Aussiebear]

@ Gasman. Thank you for the heads up, however in this case its okay since the thread was raised a month ago, and whilst Sal87 did receive some assistance it may well be that the code did not work as well as hoped.

@ Sal87. It would have been nice had you indicated this prior assistance, so that those who would like to assist you can see where you are currently situated with this issue.

[xps350]

Maybe this page https://learn.microsoft.com/en-us/of...chments-in-dao may help you.