Import txt-files using Wild-cards in file_name

[sverre]

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

 

[Douglas J. Steele]

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

 

[sverre]

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

 

[Douglas J. Steele]

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.

 

[sverre]

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

 

[Douglas J. Steele]

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?

 

[sverre]

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

Sverre