Avatar of DB-aha
 asked on

Using Variable File names in Access 2010 Macros

Hello all,

I would like to create a macro in Access 2010 that imports a series of csv files into tables. The problem is, the series of file names contains the current date at the end of the file name, yyyy-mm-dd. But if the files were generated yesterday, then the date won't be today's date. so I need the end of the file name to be seen as variable in the macro steps that call the file to be imported. Is there a way to do this?
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon

Can you give us a sample file, as well as some real file names, as they are currently formatted?
Are you only trying to import those files ending with the current date?
Jeffrey Coachman

Like Jerry, I am confused as to what your ultimate question is here...

Does this have to be a Macro, or can it be VBA?
If this must be a macro, then I am not sure if this is even possible...

If your ultimate goal is to import all files with today's date, then this is not "simple",  as there are a lot of unknowns here.

But in the most basic sense:
You have to first import any one of these files manually, and then save the import Specification.
Then you can use code like this on a button on a form to import all files with the current date:

Dim strToday As String
Dim strFolder As String
Dim strCurrentFile As String
strFolder = "C:\YourFolder\"
strToday = Format(Date, "yyyy-mm-dd")
'MsgBox strToday

strCurrentFile = Dir(strFolder & "*.csv")

    Do While strCurrentFile <> ""
        If Right(strCurrentFile, 14) = strToday & ".csv" Then
            DoCmd.TransferText acImportDelim, "TodayFile", strCurrentFile, strCurrentFile, True
        End If
        'MsgBox strCurrentFile
        strCurrentFile = Dir


The ultimate question here is, I have several CSV files with the following naming convention:

Alias_CI1UTR_2012-12-06. Everything to the left of the second underscore remains the same but the date at the end will always change. Is there a way when creating a macro to allow for the file name or at least a portion of it to be a variable?

I have not gotten into VBA yet so that would be much more difficult.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

< Is there a way when creating a macro to allow for the file name or at least a portion of it to be a variable?>
Yes, but again, ...for what purpose?
Import the current date file? (what my sample file does)
Have the user select the day to import?
Display a list of matching files?

In other words, my code uses the date as a variable, then uses that variable to import/name the table...

So you are citing the technology you want to use (variables), ...but you are not stating the ultimate goal.

<Is there a way when creating a macro >
I asked if using a macro was a hard requirement?
Is it?
If so, then as I stated, I am not sure this is possible, or easy to do.
You will have to investigate:
SetTempVars, SetLocalVars, and Text manipulation in macros

My sample illustrates this with VBA.


I am looking to make things as simple as possible. I don't really know what you mean by ultimate goal, I think I am being clear about it but I will try to explain it better.

I will be getting a series of CSV files once a month. I do not know what the date will be on the series of files from the naming convention provided above. All I know about the file names is that the second underscore and everything to the left of it will be the same only the date to the right of the second underscore will change. Once I get the CSV files, I want the macro I create to grab all of the CSV files and import them into tables. Each CSV gets it's own table. So if there are 8 CSV files, there should be 8 tables created as part of the import process.

Because I do not know what the date is at the end of the csv file names, I would assume some variable is the way to go. IF there is another solution where the majority of this functionality is implemented using a macro, please let me know. Also please let me know if you need further clarification on the description of the problem. Thanks

I cannot see how an indeterminate number of files could be imported by a macro. I go with Jeff's solution. Would have to be adjusted somewhat to deal with multiple files. (Current code only wants file whose data matches current date.)
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman


Thanks for sticking with us here and taking the time to explain.

<I want the macro I create to grab all of the CSV files and import them into tables.>
So your ultimate goal is to import "All" files *not previously imported*?

1. Then the date/file name seems irrelevant here as you are simply importing "all" files not previously imported.
It is presumed here that the source folder only contains file to be imported. (not other miscellaneous files)

2. If so, then this is a bit more complex, as you must now somehow "Log" each file name to exclude it from being imported again.
(Unless you have some sort of system already in place to delete/rename/move the imported files)

As far as Macros are concerned, ...In this case, they would not work for what you are asking for here.

So first, try my code and see if it works in the most basic sense.
Then report back to us.


At this point I think I am going to go with a SQL server solution. I will post back soon to see which works best and to let you know if what you posted makes sense and works for me. I have no experience with modules and such so your code seems foreign to me, even how to implement it.
Jeffrey Coachman

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.