MS Access auto/scheduled report export.
Results 1 to 6 of 6

Thread: MS Access auto/scheduled report export.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access auto/scheduled report export.

    Hello All,

    Is it possible to run MS Access report export (to MS Excel) by a user that does not have MS Access?

    I need to share report that uses data over ODBC to multiple remotely located users. Not all of them have MS Access. I was wondering if there was a way they could run export that would update report in Excel format.

    Thanks for your help.

    Regards,
    Rajesh

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    619
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access auto/scheduled report export.

    Access has a runtime version.?
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access auto/scheduled report export.

    If I'm understanding correctly you need to send the excel export of the access query to someone routinely?

    If so, this is something (after a lot of learning) I've managed to do quite effectively for lots of daily reports.

    First you need to create a vba function to do run the query and extract it with a "docmd.openquery" statement and designate a filename (usually a variable you create with the filepath, the name, and a variable to append the date onto the file name).

    Then you create a vba script to send them an email of that file once it's created.

    Then you create a macro in access to run that function.

    Then you create a batch file to execute that macro.

    Then you create a routine task in the windows task scheduler to execute that batch file at a given time each day.

  4. #4
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access auto/scheduled report export.

    Hello Dbc23,

    What you mentioned is correct; I need to share excel export report.

    Rather than sending email I was hoping to save excel export on shared drive. I can put Access db on that shared drive as well. I am trying to stay away from having to manually trigger that report. Reason is that not everybody that has access to the shared drive has MS Access.

    Can that be done following steps that you have listed in your post?

    Regards,
    Rajesh

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access auto/scheduled report export.

    Totally, it actually simplifies it.

    So you have your query created "querywhatever"

    You now create a function in VBA (a sub should work, but for some reason I got less issues when it's a function).

    In there you create a few variables.
    today(string) = date()
    FilePath (string) = The filepath you'll save the file to on the shared drive, including the final ""
    Filename = Filepath & "What you want the file called-" & today & ".xlsx"

    Then you execute a do.cmd

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queryWhatever", FileName, True

    It can help to wrap it with a "DoCmd.SetWarnings False" in case there's some error fields in the extracts but you should really validate the query ahead of time to make sure either those errors are OK or it's error free.

    And probably have it set to Application.Close when done

    Then you create a macro to run the function (you can use the macro builder)

    Then make a batch file and put it somewhere simple (usually root of C: is a good spot)

    Then the batch file is just a text file with a string in it like:
    "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\username\Documents\YOURDATABASE.accdb" /X NAMEOFYOURMACRO

    And task scheduler you can search for in the start menu, it's pretty straightforward to schedule the task to run a .bat file, so however often you have the task scheduled to run, it opens your batch file, which opens access and executes your VBA that runs the query and saves it as an excel file in the designated directory, bam, one less thing to do every day.


    Last edited by Fluff; Jul 19th, 2019 at 12:52 PM.

  6. #6
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access auto/scheduled report export.

    Thank you very much for your help. I will follow your guidelines and report back. I might need further help in creating batch file and task scheduler. I will get back in couple of days with more specific queries.

    Thanks again,
    Rajesh

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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