Solved

Using Variable File names in Access 2010 Macros

Posted on 2012-12-27
9
585 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now