import multiple text files into Access (different file names)

[data@discussions.microsoft.com importing data]

Is there a way to automatically import multiple text files in Access that are
in the same data format but have different file names? Thanks.

[Ken Snell [MVP]]

Yes. How would you want to do this...by VBA or by macro?

More details, please.
--

Ken Snell
<MS ACCESS MVP>

"importing data" <importing da...@discussions.microsoft.com> wrote in message
news:21D0865E-3CDD-4395...@microsoft.com...

[T@discussions.microsoft.com Rob T]

Macro (I don't know VBA). Thanks.

[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)
--

Ken Snell
<MS ACCESS MVP>

"Rob T" <Rob T...@discussions.microsoft.com> wrote in message
news:2A00C2DC-EC84-42C7...@microsoft.com...

[Rob T]

Thanks Ken. I'm entering the macro but do not see where I enter the "Control
Name" under "SetValue" in MacroStart. Also, can I remove the .txt
requirement (they are text files but just don't have the extension)?

[Ken Snell [MVP]]

The Item argument is where the ControlName goes for the macro step. Sorry
about that confusion. I've reposted the macro code correctly below.

The Dir function can be used to return a list of files without extensions,
but the way that is done will also return all other files that are in that
folder. In this situation, you'd change the

Expression: Dir("C:\MyFolder\*.txt")

to
Expression: Dir("C:\MyFolder\*")

However, ACCESS will not allow you import a text file unless it has specific
file extensions. It definitely will fail if you try to import a file with no
extension. So you will need to have those extensions on the files for this
to work. Or you'll need to change the extensions that ACCESS can use (see
http://support.microsoft.com/default.aspx?scid=kb;en-us;304206). I would
recommend that you add the ".txt" extension to the files. This can be done
via a simple VBA function if all files in the folder need to have ".txt"
added to the end of them.

----
MacroName: MacroStart

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

Condition: (none)
Action: SetValue
Item: Forms!HiddenForm!txtFile

Expression: Dir("C:\MyFolder\*.txt")

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
Item: Forms!HiddenForm!txtFile
Expression: Dir()

(end of MacroGet)
--

Ken Snell
<MS ACCESS MVP>

"Rob T" <Ro...@discussions.microsoft.com> wrote in message
news:629910BA-E67B-4E32...@microsoft.com...

[Rob T]

I keep getting an error msg "expression you entered has function name that
office can't find" somewhere in the MacroStart SetValue step. I already
created the form, the textbox and MacroGet. Here's what I've got in my
MacroStart SetValue:

item: [Forms]![HiddenForm]![txtFile]
expression: Dir("C:\Documents and Settings\rthiemann\My Documents\Water
Recovery\Banner Data\MeterData\*.txt")

any suggestions?

[Ken Snell [MVP]]

Which version of ACCESS are you using?

Your path to the file is a bit unusual. Are you using the My Documents
standard folder as the "root"? If yes, the path should be this, I think:

"C:\My Documents\Water Recovery\Banner Data\MeterData\*.txt

Otherwise, put a text file in the C drive's main folder, and let's see if it
works that way:

"C:\MyTextFileName.txt"

--

Ken Snell
<MS ACCESS MVP>

"Rob T" <Ro...@discussions.microsoft.com> wrote in message

news:A173D537-A241-4BAC...@microsoft.com...

[Rob T]

I'm using Access 2003, SP1. I tried changing the path both ways (and copying
the file to the new path) but I still get the same error msg. I did notice
that, when I try to write the SetValue Expression using the expression
builder, "Dir" is not listed as a function. Could this be part of the
problem?

[Ken Snell [MVP]]

Hmmm.. I tested the macro code in ACCESS 2002, so I would be surprised if
2003 doesn't allow it to work.

You may have a reference problem. Try following these steps (from a post by
Douglas Steele, MVP):

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

"Rob T" <Ro...@discussions.microsoft.com> wrote in message

news:FA66C243-9EFA-4BB5...@microsoft.com...