Solved

Using Variable File names in Access 2010 Macros

Posted on 2012-12-27
9
617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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