import multiple text files into Access (different file names)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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>
 
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)?
 
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>
 
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?
 
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 said:
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 said:
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)
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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>


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

:

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>




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

:

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

More details, please.
--

Ken Snell
<MS ACCESS MVP>

in
message
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?

:

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>


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

:

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>




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

:

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

More details, please.
--

Ken Snell
<MS ACCESS MVP>

"importing data" <importing (e-mail address removed)>
wrote
in
message
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.
Click to expand...
Click to expand...
Click to expand...
 
I know this is a very old thread, but I'm needed help with something similiar. I'm needed to import 30/31 text files into a monthly database. All the text files are in this format 110112.txt 110212.txt etc.

Any help is appreciated.
 
Back
Top