Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Question Unanswered: Export Query to Excel Question

    Hello,

    I am using a Macro ( don't know VBA) to export a query to a drive to save for history purposes. This file will be exported every month with different data in it for that prevous months usage.

    I can get the macro to export the file however the name is my problem. Is there a way to save the file automatically with the Current date as part of the file name???? Right now it exports as: New Additons.xls

    I would like to to export as "New Additons_07222011.xls or even New Additons_July 22, 2011. Any variation on the date would be fine by me.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You can't do this with just a macro, you'll have to put some VBA in to achieve this.

    If you tell me how the macro is being run (i.e. do you click a button on a form? If so, what is the buttons name?), where you're saving it to (C:\blah\blah), what you want the filename to look like exactly (myExcelFile_22-07-2011.xls), then I can give you some code and tell you how to insert it.

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    The macro is being run from a Form using a Toggle Button and a Group.

    The Group is CDG and the Button number is 9. So in teh Macro under Condition it shows as [CDG]=9 then the Action etc.

    The Path is: \\eajrpfs01\myname$\Charge Master\Med Assets Files\FY12 Med Assets Files\New Additions_07-22-2011.xls

    File name would be as above "New Additions_07-22-2011.xls.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree not sure you can do it with a macro, but you can in VBA

    whilst yoiu are at it I'd suggest you change the filename date format to, say, the ISO date format yyyy-mm-dd so the filenames will be in date order.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Okay, sorry about the delay, been off over the weekend and my internet is out at home at the moment.

    1) You want to push Alt+F11.
    2) Expand the name of your database file (i.e. myDatabase) in the 'Projects' window on the left hand side.
    3) Double-click the name of the form you're working on.
    4) Select from the dropdown box above the code pane, labelled '(General)', the name of the button.
    5) Select 'Click' from the 'Declarations' dropdown
    6) Then, you want to copy & paste the following, excluding the 'Private Sub blah blah blah', which should of been generated for you when completing steps 4 and 5.

    Code:
    Private Sub aNameWillBePutHere_Click
    
    Dim strQryName As String
    Dim strXLFile As String
    
    strQryName = "Name Of Your Query Here"
    
    strXLFile = "\\eajrpfs01\myname$\Charge Master\Med Assets Files\FY12 Med Assets Files\New Additions_" & str(Format(Date, DD-MM-YYYY)) & ".xls"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strQryName, strXLFile
    
    End Sub
    Replacing "Name Of Your Query Here", with whatever your query is called (leaving the " " there).

    Any issues, let me know.

    PS: I noticed you use \myname$\ in the file directory. If that's refering to your username (I suspect it is), the above won't work, and you'll have to dynamically apply the username. Not a problem, but requires an extra couple of lines of code.

  6. #6
    Join Date
    Sep 2012
    Posts
    3
    Can I run this from VBA Module/Function and not a button?

Posting Permissions

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