Transfer Multiple Text Files

[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

 

[Steve Schapel]

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.

 

[Ken Snell [MVP]]

Or use the RepeatMacro action that has a condition based on Dir function?

 

[Steve Schapel]

Hi Ken,

I can't quite work out how this would work... can you expand?

 

[Ken Snell [MVP]]

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

 

[Chris Reveille]

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

 

[Ken Snell [MVP]]

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>

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>

 

[Ken Snell [MVP]]

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>