Import txt-files using Wild-cards in file

Import txt-files using Wild-cards in file_name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sverre - 20 May 2008 12:31 GMT

Hi

I have several txt-files which I would like to import in one shot. I tried
with *.txt as file-name to make the macro import all txt-files, but it did
not work. I got an error message saying that the file does not exist.

Is there any way to make the macro to import all files in a specific folder
in one shot?

Best regards
Sverre

Reply to this Message

Douglas J. Steele - 20 May 2008 13:01 GMT

No, there isn't. However, you can create a loop that gets the files one at
a time, and import them that way.

Something like:

Dim strFolder As String
Dim strFile As String

strFolder = "E:\Some Folder\Some Subfolder\"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
' Put your code to import strFolder & strFile here
strFile = Dir()
Loop

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi
>
[quoted text clipped - 8 lines]
> Best regards
> Sverre

Reply to this Message

sverre - 20 May 2008 13:50 GMT

OK, thank you. But how do I write the code for importing? I am not familiar
with vba-programming.
I defined a macro with a link specification for the txt-file import.
Can I call on that macro in the VBA code you suggested?

I tried this, but it did not work:

Sub IMPORT_OF_EXTRACTS()
Dim strFolder As String
Dim strFile As String

strFolder =
"\\fspa\fileroot\DFS.8803.gemdisk\Projekt\Refaet\MAP\Avstämning_SESAM_Jeevesextrakt\extraktimport"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
' Put your code to import strFolder & strFile here
Call macro("ÖÖ IMPORT AV EXTRAKT")

strFile = Dir()
Loop

End Sub

Best regards
Sverre

> No, there isn't. However, you can create a loop that gets the files one at
> a time, and import them that way.
[quoted text clipped - 23 lines]
> > Best regards
> > Sverre

Reply to this Message

Douglas J. Steele - 20 May 2008 17:01 GMT

You'd be far better off just calling the TransferText method. Unfortunately,
since I don't know what options you need, I can't really direct you any
further than that. Assuming your file is delimited (as opposed to
fixed-width) and has field names, and you want to import it to a table
contained in variable strTable, it would be something like:

DoCmd.TransferText acImportDelim, , strTable, strFolder & strFile, True

Note that the value for strFolder must end with a slash.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> OK, thank you. But how do I write the code for importing? I am not
> familiar
[quoted text clipped - 53 lines]
>> > Best regards
>> > Sverre

Reply to this Message

sverre - 20 May 2008 19:33 GMT

Hi again,

I worked on your hint and am coming closer to a solution (I believe) but
there are two things that make me unsure:

1. The execution stops with an error message on:
"strFile = Dir(strFolder & "*.txt")" and gives me error code 52 with
explanation "wrong file name or file no".

2. In the transfer-text syntax for file-name -what do I put in ? Exactly the
phrase "Strfolder" & "StrFile" ??

The VBA looks like this:
Sub GG()
Dim strFolder As String
Dim strFile As String

strFolder = "C:\temp\"

strFile = Dir(strFolder & "*.txt")

Do While Len(strFile) > 0
' Put your code to import strFolder & strFile here

DoCmd.TransferText acImportFixed, "Bokföringsextrakt_import_spec",
"bokföringen", "StrFolder & StrFile", False, False
strFile = Dir()
Loop
End Sub

Best regards
Sverre

> You'd be far better off just calling the TransferText method. Unfortunately,
> since I don't know what options you need, I can't really direct you any
[quoted text clipped - 63 lines]
> >> > Best regards
> >> > Sverre

Reply to this Message

Douglas J. Steele - 20 May 2008 19:44 GMT

No quotes around StrFolder or StrFile:

DoCmd.TransferText acImportFixed, "Bokföringsextrakt_import_spec",
"bokföringen", StrFolder & StrFile, False, False

Assuming the Error 52 occurred with the actual code you posted, do you have
a folder named C:\Temp on your machine, and do you have at least Read
permission on the folder?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi again,
>
[quoted text clipped - 101 lines]
>> >> > Best regards
>> >> > Sverre

Reply to this Message

sverre - 21 May 2008 07:36 GMT

The temp-folder was write protected. Many thanks for
your time Douglas!

Sverre

> No quotes around StrFolder or StrFile:
>
[quoted text clipped - 110 lines]
> >> >> > Best regards
> >> >> > Sverre

Reply to this Message

Rate this thread: