Import All Files in a Specified Folder

  • Thread starter Thread starter jenni
  • Start date Start date
J

jenni

hello -
I have included my VBA code below. This is to import
all .csv files in a specified folder to one table.
I keep getting an error that points to the
Do.Cmd.TransferText line. But I cant figure out what it
is.
Any ideas????
Thanks,
Jenni
...........................................................
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:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format")
strfile = "Dir([*.*])"
Do While Len(strfile) > 0
DoCmd.TransferText [acImportDelim], [specs_Import472],
[tbl_472_Import], [("C:\Documents and
Settings\jpfeifer.CCI\My Documents\Mech\PR
project\New472Format") & ("strfile")], [False]
'delete the file (consider moving it to an Archive
folder instead.)
Kill "C:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format" & strfile
strfile = Dir
Loop

End Sub
 
At quick glance it looks like you need a backslash (\)
after the New472format folder in both the transfertext and
delete file lines.
i.e.:
DoCmd.TransferText [acImportDelim], [specs_Import472],
[tbl_472_Import], [("C:\Documents and
Settings\jpfeifer.CCI\My Documents\Mech\PR
project\New472Format\") & ("strfile")], [False]
Kill "C:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format\" & strfile
 
jenni said:
hello -
I have included my VBA code below. This is to import
all .csv files in a specified folder to one table.
I keep getting an error that points to the
Do.Cmd.TransferText line. But I cant figure out what it
is.
Any ideas????
Thanks,
Jenni
..........................................................
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:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format")
strfile = "Dir([*.*])"
Do While Len(strfile) > 0
DoCmd.TransferText [acImportDelim], [specs_Import472],
[tbl_472_Import], [("C:\Documents and
Settings\jpfeifer.CCI\My Documents\Mech\PR
project\New472Format") & ("strfile")], [False]
'delete the file (consider moving it to an Archive
folder instead.)
Kill "C:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format" & strfile
strfile = Dir
Loop

End Sub
Click to expand...

There are a number of problems with your code. Try this:

'----- start of revised code (AIR CODE) -----
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
Dim strFolder As String

strFolder = _
"C:\Documents and Settings\jpfeifer.CCI\My Documents\" & _
"Mech\PR project\New472Format\"

strfile = Dir(strFolder & "*.*)

Do While Len(strfile) > 0

DoCmd.TransferText _
acImportDelim, _
"specs_Import472",
"tbl_472_Import",
strFolder & strfile, _
False

'delete the file (consider moving it to an Archive
'folder instead.)
Kill strFolder & strfile

strfile = Dir

Loop

End Sub
'----- end of revised code -----

That's only air code, but it should get you a lot closer.
 
Dirk -
Worked like a charm! You're best!
Thank you.
Jenni
-----Original Message-----
hello -
I have included my VBA code below. This is to import
all .csv files in a specified folder to one table.
I keep getting an error that points to the
Do.Cmd.TransferText line. But I cant figure out what it
is.
Any ideas????
Thanks,
Jenni
....................................................... ....
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:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format")
strfile = "Dir([*.*])"
Do While Len(strfile) > 0
DoCmd.TransferText [acImportDelim], [specs_Import472],
[tbl_472_Import], [("C:\Documents and
Settings\jpfeifer.CCI\My Documents\Mech\PR
project\New472Format") & ("strfile")], [False]
'delete the file (consider moving it to an Archive
folder instead.)
Kill "C:\Documents and Settings\jpfeifer.CCI\My
Documents\Mech\PR project\New472Format" & strfile
strfile = Dir
Loop

End Sub
Click to expand...

There are a number of problems with your code. Try this:

'----- start of revised code (AIR CODE) -----
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
Dim strFolder As String

strFolder = _
"C:\Documents and Settings\jpfeifer.CCI\My Documents\" & _
"Mech\PR project\New472Format\"

strfile = Dir(strFolder & "*.*)

Do While Len(strfile) > 0

DoCmd.TransferText _
acImportDelim, _
"specs_Import472",
"tbl_472_Import",
strFolder & strfile, _
False

'delete the file (consider moving it to an Archive
'folder instead.)
Kill strFolder & strfile

strfile = Dir

Loop

End Sub
'----- end of revised code -----

That's only air code, but it should get you a lot closer.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top