Solved

Using Variable File names in Access 2010 Macros

Posted on 2012-12-27
9
596 Views
Last Modified: 2013-01-17
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?
0
Comment
Question by:DB-aha
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38725649
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38726143
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
0
 
LVL 1

Author Comment

by:DB-aha
ID: 38727002
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727363
< 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
0
 
LVL 1

Author Comment

by:DB-aha
ID: 38736684
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
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38736987
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.)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38737509
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
0
 
LVL 1

Author Comment

by:DB-aha
ID: 38740905
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38741658
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question