Exporting query to Excel file saved as attachment in my database

[Sal87]

I am trying to export my query to an excel file which it is saved as attachment in my database ,but i dont know how to do that in VBA so here is my code:
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook
Dim wsSheet1 As Excel.Worksheet
Dim i As Long
Set cnn = CurrentProject.Connection
' strSQL = "SELECT * FROM myquery"
strRecordsetDataSource = "Select * from my table"
Set rst = CurrentDb.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)


Set appXL = CreateObject("Excel.Application")
Const strTable = "tbl_attachement" '<- my table which contains the Excel Template


Const strField = "attach" '<- the field which contains the excel file
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tbl_attachement WHERE ID=1")
Set rstChild = rst.Fields(attach).Value



Set wsSheet1 = rstChild.Sheets("help") '' the excel sheet which i want to be filled with my query
wsSheet1.Select
For i = 0 To rst.Fields.Count - 1
wsSheet1.Activate
wsSheet1.aOffset(0, i).Value = rst.Fields(i).Name
Next
wsSheet1.Range("a2").CopyFromRecordset rst
wsSheet1.Columns("A:Q").EntireColumn.AutoFit
rst.Close

[ranman256]

vFile = "C:\mypath\files\ExportFile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xls, "qsMyQuery", vFile, True
Send1Email "wileE@acme.com", "your file", "look at this file", vFile

'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark MICROSOFT OUTLOOK OBJECT LIBRARY in the vbE menu, Tools, References
'-------


Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, optional pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem


On Error GoTo ErrMail




Set oApp = GetApplication("Outlook.Application")
'Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)


With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody


    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
    
    .Send
'    .Display True


End With


Email1 = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function






Function GetApplication(className As String) As Object
' function to encapsulate the instantiation of an application object
Dim theApp As Object
On Error Resume Next
Set theApp = GetObject(, className)
If Err.Number <> 0 Then
    MsgBox "Unable to Get" & className & ", attempting to CreateObject"
    Set theApp = CreateObject(className)
End If


If theApp Is Nothing Then
    Err.Raise Err.Number, Err.Source, "Unable to Get or Create the " & className & "!"
    Set GetApplication = Nothing
End If


'MsgBox "Successfully got a handle on Outlook Application, returning to caller"
Set GetApplication = theApp
End Function

[Sal87]

Hi Ranman ,
I dont understand your code , my problem is how to export my query to an existing excel file which i saved it on my access database as attachment , so this template should be filled out with the data from my query .

[Sal87]

Hi Ranman ,
I dont understand your code , my problem is how to export my query to an existing excel file which i saved it on my access database as attachment , so this template should be filled out with the data from my query.

[Welshgasman]

You would need to export the attachment to a folder. Add your new data and reimport back to the DB.
Always better to just have a link, as atachments will bloat your DB.

[Sal87]

thanks , but how can i do that?

[Welshgasman]

thanks , but how can i do that?

No idea as to the code TBH. I would Google those phrases I used, or possibly 'Save Access attachments to disk' and then the reverse, if you insist on putting them back in the DB.
I never kept attachments in any of my DBs, so never needed to explore that option.

[Sal87]

thanks , my idea is just to put my query result in the template and show the excel file then the user can save it whenever he wants so the most important is to fill the template with the data.

[Welshgasman]

thanks , my idea is just to put my query result in the template and show the excel file then the user can save it whenever he wants so the most important is to fill the template with the data.

https://www.google.com/search?q=acce...hrome&ie=UTF-8

[alansidman]

Here's an alternative to VBA with Power Query

https://www.youtube.com/watch?v=I737aAvH1G8

[Sal87]

I changed trhe code to sabe the file on the hard drive and then open the "help tab" to fill it out with the query result but it didnt work.

Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook
Dim wsSheet1 As Excel.Worksheet
Dim i As Long
Set cnn = CurrentProject.Connection
strSQL = "SELECT * FROM myquery"
strRecordsetDataSource = "Select * from my table"
Set rst = CurrentDb.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

Set appXL = CreateObject("Excel.Application")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tbl_attachement WHERE ID=1")
Set rstChild = rst.Fields(attach).Value

Set rstChild = rst.Fields(attach).Value ' the .Value for a complex field returns the underlying recordset.
Set wsSheet1 = wb.Sheets("help")
wsSheet1.Select
For i = 0 To rst.Fields.Count - 1
wsSheet1.aOffset(0, i).Value = rst.Fields(i).Name
wsSheet1.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
Next
rst.Fields(attach).LoadFromFile rst
wsSheet1.Range("a2").CopyFromRecordset rst
wsSheet1.Columns("A:Q").EntireColumn.AutoFit
rst.Close
End sub

[Welshgasman]

Now crossposted at http://www.vbaexpress.com/forum/show...ccess-database