Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Are you only trying to import those files ending with the current date?
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
Loop
JeffCoachman
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
Loop
JeffCoachman
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.
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.
< 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.
JeffCoachman
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.
JeffCoachman
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 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.)
DB-aha,
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.
JeffCoachman
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.
JeffCoachman
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.
Well it works, so all you would have to do is modify it to bring in "All Files"
'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
Loop
...If you posted a sample CSV file, I could probably modify it to work in the way you want here.
'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
Loop
...If you posted a sample CSV file, I could probably modify it to work in the way you want here.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial