Import All Files In Directory
ChrisR
.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]
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)
--
Ken Snell
<MS ACCESS MVP>
"ChrisR" <c_re...@hotmail.com> wrote in message
news:epEcRIHP...@tk2msftngp13.phx.gbl...
ChrisR
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]" <kthsne...@ncoomcastt.renaetl> wrote in message
news:e37oDNHP...@TK2MSFTNGP12.phx.gbl...
Ken Snell [MVP]
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" <c_re...@hotmail.com> wrote in message
news:eYg5YVHP...@TK2MSFTNGP10.phx.gbl...
ChrisR
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-
"Ken Snell [MVP]" <kthsne...@ncoomcastt.renaetl> wrote in message
news:uOacXdHP...@TK2MSFTNGP10.phx.gbl...
ChrisR
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-
"ChrisR" <c_re...@hotmail.com> wrote in message
news:eAufcxHP...@tk2msftngp13.phx.gbl...
Ken Snell [MVP]
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
"ChrisR" <c_re...@hotmail.com> wrote in message
news:eAufcxHP...@tk2msftngp13.phx.gbl...
Ken Snell [MVP]
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" <c_re...@hotmail.com> wrote in message
news:%23nmvE2H...@TK2MSFTNGP15.phx.gbl...
ChrisR
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]" <kthsne...@ncoomcastt.renaetl> wrote in message
news:u6c1cDIP...@TK2MSFTNGP14.phx.gbl...
Ken Snell [MVP]
--
Ken Snell
<MS ACCESS MVP>
"ChrisR" <c_re...@hotmail.com> wrote in message
news:%23g52AKI...@TK2MSFTNGP14.phx.gbl...