Results 1 to 4 of 4
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,060

    Automate Export of a table to an Excel Spreadsheet

    I have a SQL Server routine that creates a table 3 times per day. All I want Access to do is Export the table all 3 times to Excel and email the Excel file to a number of recipients. I've been unable to test the email portion because the Export gives me an error in this subroutine

    This is the error: Cannot update. Database or object is read-only. I'm not trying to update anything I just want to export the table to Excel

    Code:
    Public Sub Createfile()
    Dim filename As String
    On Error GoTo Err_Handler
    
    
    
    Select Case Time
        Case Is < #10:00:00 AM#
            filename = "Daily_Referral_Spend_for_" & Format(Now() - 1, "mmddyyyy") & "_final_Run.xslx"
        Case #10:00:00 AM# To #1:00:00 PM#
            filename = "Daily_Referral_Spend_for_" & Format(Now(), "mmddyyyy") & "_Noon_Run.xslx"
        Case Else
            filename = "Daily_Referral_Spend_for_" & Format(Now(), "MMDDYYYY") & "_Evening_Run.xslx"
    End Select
    DoCmd.TransferSpreadsheet acExport, 8, "dbo_Auths_Daily_Report", filename, True
    Exit_handler:
    Exit Sub
    Err_Handler:
    MsgBox Err.Description
    GoTo Exit_handler
    Return
    
    End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,752
    Not easy.

    Access forms have a timer event but as I understand it can be complicated and processor hog. I've never used.

    I have used Windows Task Scheduler to open a VBScript file that opens Access db and runs a procedure. Example:

    Set accessApp = CreateObject("Access.Application")
    accessApp.OpenCurrentDataBase "C:\Users\June\Umpires.accdb"
    accessApp.Run "Test"
    accessApp.Quit


    The file extension is wrong, should be xlsx not xslx.

    Use acSpreadsheetTypeExcel12Xml (or 10 if you prefer) instead of 8.

    Need full destination file path/name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,060
    I don't use the timer event. I use the Windows Task Scheduler to open the accdb file and the Autoexec Macro to run the Subroutine.

    Destination File name is built in the Code I provided the path is the documents folder on my local harddrive.

    I've come up with a temporary workaround by removing the automation part of it. The Accdb file is still run at the appropriate times but I changed the DoCmd To
    DoCmd.SendObject acSendTable, "dbo_Auths_Daily_Report", acFormatXLS, "RMilhon@hdmg.net;mdaalborg@hvvmg.com;MPAHL@hvvmg. com;drock@hdmg.net;rhowells@hdmg.net;slmeehan@hvvm g.com", , , "test", ""

    It works but I have to manually click on the Send button when the email to be sent opens on my desktop. Really need to fix the automation.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,612
    Destination File name is built in the Code I provided
    I don't understand that statement. Your file has an extension of xslx?
    An unrecognized file extension is a documented reason for the error message when exporting/importing. You could try doing the export manually and see if it works. If not, it's likely an issue outside of Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-16-2017, 06:53 PM
  2. VBA to automate creation of pivot table in excel
    By SteveApa in forum Programming
    Replies: 1
    Last Post: 01-27-2015, 11:37 AM
  3. Replies: 2
    Last Post: 12-12-2014, 04:42 PM
  4. Replies: 1
    Last Post: 05-23-2013, 10:00 AM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums