Transfer Multiple Text Files

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

Guest

Howdy,

What I am trying to do is import around 30-31 text files in one hit,
currently I have a transfer text for each file. is there a way of importing
all files in one go rather the 30-31 transfer texts macro actions?

Cheers
 
Songoku,

No, a separate TransferText action is required for each text file.

If you used a VBA procedure instead of a macro, it may be possible to
loop through the files, depending on their file names, and the names of
the tables you are importing into, etc.
 
Hmmm.... this is one of those things that I "saw" in my head in a flash and
didn't think it all the way through < g >.

It would take a bit of finagling...one would need to use a "storage" place
(control on a hidden form, for example) to hold the filename returned by the
Dir function (recursive call or first time call).

OK - new form "HiddenForm" would have one textbox: txtFile.

Something like this?

----
MacroName: MacroStart

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

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

(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
 
You could use code instead of a macro. I got this from a
previous post by Joe Fallon.
How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII
delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName",
"AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder
instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub
 
Slight modification:

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



Ken Snell said:
Hmmm.... this is one of those things that I "saw" in my head in a flash
and didn't think it all the way through < g >.

It would take a bit of finagling...one would need to use a "storage" place
(control on a hidden form, for example) to hold the filename returned by
the Dir function (recursive call or first time call).

OK - new form "HiddenForm" would have one textbox: txtFile.

Something like this?

----
MacroName: MacroStart

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

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

(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

--

Ken Snell
<MS ACCESS MVP>
Click to expand...
 
Just noted a typo in the first action for the MacroGet macro .......

----
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>
 
Back
Top