Results 1 to 8 of 8
  1. #1
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31

    Saved Imports with Changing File Names

    Is it possible to make a saved import where a portion of the name of the file to be imported always changes? For example I have:



    Location1 Inventory File 06/10/2021.exe
    or
    Location1 Inventory File 10June2021.exe

    The part which always changes is the date, the portion before the date is always the same and is unique.



    Similar, some of the locations use the date as the tab name, others just leave it at default sheet1. The sheet1 are obviously no problem as its always the same. But the date named tabs I'm not sure? One thing on those, it is always the first tab on all of the spreadsheets.

    Currently I go in and manually change the name of the files removing the dates and the name of the tabs to the location name but would like to be be able to automate it.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Are you familiar with VBA? Tn that case you can build up the import string according to some parameters. Of course there has to be some logic in the names.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I assume the files are Excel (.xlsx) not executables (.exe). Yes, you can automate importing of Excel files with any name given they are in a known location\folder.
    You would loop through all files in said folder, if file is an Excel file (you can even check for keywords -
    Code:
    If Instr(sFileName,"Inventory") >0 Then 'this is an inventory file
    ) and import the first sheet (using sheet index instead of sheet name to deal with the inconsistencies).
    Here are some links:
    https://www.datanumen.com/blogs/impo...-access-table/
    https://www.access-programmers.co.uk...ectory.135603/

    Show us what you have tried if you get stuck.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Why not just rename the file to the one used on the saved import spec?

  5. #5
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    I currently rename the files and the tabs, but as the number of files increase and being a daily function, its starting to get tedious.

  6. #6
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    Thank you, I will give this a try! I'll let you know how it works.

    BTW, yes, excel not exe

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Johnny12 View Post
    I currently rename the files and the tabs, but as the number of files increase and being a daily function, its starting to get tedious.
    I meant automate the renaming?
    If you put them in a folder you could process each in tunr, rename, import, rename as used, and get the next file.
    As to the sheet names, either use the first sheet, or a similar process using Excel.?

    Remember you can record Excel macros, that will give you a head start of locating correct sheet, renaming and possibly moving to first sheet in workbook.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition to Vlad's links, check out Ken Snell's site

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

Similar Threads

  1. Databases/Queries associated with Saved Imports/Exports
    By moody6086 in forum Import/Export Data
    Replies: 1
    Last Post: 08-16-2019, 09:06 AM
  2. Saved imports and exports disappeared from manage data tasks view
    By mmoria2312 in forum Import/Export Data
    Replies: 8
    Last Post: 11-07-2016, 05:07 PM
  3. pass paramaters to query in saved imports
    By nirvana in forum Import/Export Data
    Replies: 9
    Last Post: 03-15-2013, 07:58 AM
  4. Changing File Location in saved imports using VBA
    By akshatagarwal93 in forum Import/Export Data
    Replies: 3
    Last Post: 07-23-2012, 10:47 AM
  5. Access 2010 Saved Imports
    By Tomfernandez1 in forum Access
    Replies: 1
    Last Post: 09-27-2011, 12:27 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