Consulting

Results 1 to 4 of 4

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

  1. #1
    VBAX Newbie
    Joined
    May 2023
    Posts
    1
    Location

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

    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
    Last edited by Aussiebear; 05-08-2023 at 12:02 PM. Reason: Added code tags to supplied code

  2. #2
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    @ 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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Groeten,

    Peter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •