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.
in the same data format but have different file names? Thanks.
Ken Snell said:Yes. How would you want to do this...by VBA or by macro?
More details, please.
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 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.
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.