Results 1 to 6 of 6

Thread: Auto Naming an exported Access Table to a text (or) excel file

  1. #1
    Elbows is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    8

    Auto Naming an exported Access Table to a text (or) excel file

    I have tried a few modules and macro's but without a deep-seated knowledge of VBA I'm a little stuck.

    All I need to do is to export my table data [GOODS IN] every evening (by pressing a command button).
    This exports the table to a specified folder......
    However I need it to auto name the exported file by date and time (at the point at which the command is started).

    Many Thanks
    Ellis

  2. #2
    aytee111 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    559

  3. #3
    Elbows is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    8
    Hi aytee111.
    I have already seen this link and get most of it. I just simply can't quite understand it though!!
    The module reads like so:

    Public Function DynamicFileNameExampleFunction()
    Dim Current_Date As String
    Dim File_Name As String
    Current_Date = Format$(Now(), "yyyy\-mm\-dd hhmmssAMPM")
    File_Name = "Z:/ExampleFolder/ExampleExport-" + Current_Date + ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TestTable", [File_Name]
    End Function

    I can understand how to put this into a module and then create a macro to run it but I'm unsure how to associate the Access table that I want to export from within my database and exported to a file location.
    As I mentioned my table name is called [Goods In] and the destination would be C:/GOODS IN/TEXT FILES

  4. #4
    aytee111 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    559
    Here's another version - put this is the OnClick event of your command button:
    (this is for exporting to Excel)
    Note - don't copy and paste this code. Type it up yourself. You will see what Access gives you when you are typing the DoCmd statement, it tells you what it wants each step of the way and you can customize it for your own ends.

    Private Sub CmdX_OnClick()
    Dim FileName As String
    FileName="C:\Goods In\Text Files\??file name??"
    DoCmd.TransferSpreadsheet acExport,,"Goods In",FileName & Format(Date(),"yyyymmdd")
    End Sub

    To export to a text file, first create the export specs and save them.
    Then change the above to DoCmd.TransferText... again type it and Access will tell you what to type in.

  5. #5
    Elbows is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2011
    Posts
    8
    Simply...thank you.
    Baldrick I Love You X

  6. #6
    aytee111 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    559
    You're welcome!

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

Similar Threads

  1. Auto Update an Exported Excel Spreadsheet
    By halt4814 in forum Access
    Replies: 1
    Last Post: 01-18-2012, 02:30 PM
  2. to open Exported Excel File
    By waqas in forum Programming
    Replies: 3
    Last Post: 10-16-2011, 11:33 AM
  3. Adapting Excel File to Access Table
    By mbake085 in forum Access
    Replies: 2
    Last Post: 05-18-2011, 09:41 AM
  4. Importing text file into Access Table
    By Anthony in forum Import/Export Data
    Replies: 13
    Last Post: 09-23-2009, 03:47 PM
  5. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 02:02 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
  •  
Tech Forums: Microsoft Office Forums