Import All Files In Directory

[ChrisR]

I have a weekly process where another non MS app give me 30 e-mails with
..csv attachments. I can quickly dump the 30 files into one otherwise empty
directory. I have an Access macro that clears an Import table, uses
TransferText to import a csv file, appends certain info to one table, then
other info to a second table.

What I would like to do is have the macro cycle through each file in the
directory performing these actions on each file until all have been
processed (regardless of name because they always have system generated
names).

Any thoughts??

Open to VBA if it is easy to understand, I am not that well versed in VBA,
just macros and general query/table design.

Thanks in advance for any ideas/help.

c-

 

[Ken Snell [MVP]]

I posted one method in the macros newsgroup a short time ago. It assumes
that all files are in the same folder, and all end with ".txt" extension. It
also uses a "hidden" form that is opened by the macro to store the filename
info as the macros do their things -- the concept is based on using the Dir
function recursively (just as it's done in VBA), and using a textbox on that
hidden form to store data for the macro to read.

Create a form (name it "HiddenForm") that has a single textbox (unbound) on
it (name the textbox "txtFile").

Create the two macros shown below. Change arguments' information to match
your setup.

To run the process, you would run MacroStart.

----
MacroName: MacroStart

Condition: (none)
Action: OpenForm
Form Name: "HiddenForm"
Mode: Hidden

Condition: (none)
Action: SetValue
Expression: Dir("C:\MyFolder\*.txt")
Control Name: Forms!HiddenForm!txtFile

Condition: (none)
Action: RunMacro
Macro Name: MacroGet
Repeat Expression: Len(Forms!HiddenForm!txtFile & "") > 0

Condition: (none)
Action: Close
Object Type: Form
Object Name: HiddenForm


(end of MacroStart)

----

MacroName: MacroGet

Action: TransferText
File Name: ="C:\MyFolder\" & Forms!HiddenForm!txtFile
(other arguments as appropriate)

Action: SetValue
Expression: Dir()
Control Name: Forms!HiddenForm!txtFile


(end of MacroGet)

 

[ChrisR]

Ken,

Thanks for the quick response. I will follow your directions and see if I
can make it work. I understand the concept.

Just to make sure I am understanding, the area where I would add my existing
commands to run delete and append queries would be as the last steps in the
MacroGet is that correct?

c-

 

[Ken Snell [MVP]]

Not as the last steps, but as steps 2 and 3:

MacroName: MacroGet

Action: TransferText
File Name: ="C:\MyFolder\" & Forms!HiddenForm!txtFile
(other arguments as appropriate)

Action: OpenQuery (the delete query)

Action: OpenQuery (the append query)

Action: SetValue
Expression: Dir()
Control Name: Forms!HiddenForm!txtFile

--

Ken Snell
<MS ACCESS MVP>


(end of MacroGet)

 

[ChrisR]

Ken,

Almost there I think. I changed everything you had as txt to csv since that
is the file format I have.

The error I am getting is in the Transfer Text command.

I open the hidden form (I checked it and it is correctly displaying name of
first file), then set value to have Item = the name of text box and the
expression as Dir("C:\STEPFiles\*.csv). Up to this point, no errors.

Then I run the Get macro and it fails saying C:\STEPFiles\' is not a valid
path.....

Something in the TransferText action is not working.

I have...
Type = Import Delmited
Spec Name = correct spec I currently use to import 1 at time
Table Name = destination table name
File Name = "C:\STEPFiles\"&Forms!HiddenForm!csvFile
FieldNames = No
HTML and Code Page are left empty.

Access is somehow not recognizing the merge of the text saying the path and
the form telling it the file name.

Any thoughts??

c-

 

[ChrisR]

Ken,

Hope I can catch you before you reply.

Another question, is how does it cycle through the files? How does the
macro to go to the next .csv file after it imports the first one?

c-

 

[Ken Snell [MVP]]

Put an = sign in front of the file name and put spaces around the & sign:

File Name = ="C:\STEPFiles\" & Forms!HiddenForm!csvFile

So what you should have in the File Name box is (with the = sign included)
--

Ken Snell
<MS ACCESS MVP>

="C:\STEPFiles\" & Forms!HiddenForm!csvFile

 

[Ken Snell [MVP]]

Dir is a function that can be called recursively. When you don't put an
argument in the function call, it cycles to the next file that was found
during the very first search (the *.csv search). When no more files are in
that first search array, the Dir function returns an empty string.

Therefore, in the last step of the MacroGet macro:
Action: SetValue
Expression: Dir()
Control Name: Forms!HiddenForm!txtFile

the macro is getting the next file in the first search array.


In the second-to-last step of MacroStart:
Condition: (none)
Action: RunMacro
Macro Name: MacroGet
Repeat Expression: Len(Forms!HiddenForm!txtFile & "") > 0

the macro tests the length of the string returned by the last step of the
MacroGet macro. When it's empty (length = 0), the MacroStart macro goes to
the last step, which closes the hidden form and ends the macro sequence.
--

Ken Snell
<MS ACCESS MVP>

 

[ChrisR]

Works like a charm now.

Thank you so much for the help. This will save me a bunch of time every
morning.

It will be even more efficient when I figure out how to automagically have
Outlook save a copy of the attached file to the directory automatically when
it arrives. But that is a question for the Outlook newsgroup.

Again, thanks for the help and the follow to all of my questions.

c-

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Works like a charm now.

Thank you so much for the help. This will save me a bunch of time every
morning.

It will be even more efficient when I figure out how to automagically have
Outlook save a copy of the attached file to the directory automatically
when
it arrives. But that is a question for the Outlook newsgroup.

Again, thanks for the help and the follow to all of my questions.

c-