Importing Text Files Using Macros

[ammdumas]

This is my current macros:

ActiveSheet.Unprotect
With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:TOIMPORT.txt", _
Destination:=Range("A4"))
.Name = "TOIMPORT"
.FieldNames = True
.RowNumbers = False... etc. etc.

Instead of TOIMPORT (which is a re-naming), how can I get Excel to find a text file where the last few characters (in this case *used at.txt) are always the same. The text file is generated by another system (where the last few char. are the same). Can I use a wildcard? I tried the "*" and it didn't work.

[ This Message was edited by: ammdumas on 2002-04-23 11:53 ]

[John McGraw]

I dont have time to offer code right now, but I'll give you a hint. Use the dir() function to return each file according to specs in the directory.

dir("*.txt") would return the first .txt file. if you do a dir("*.txt") again in the code, it returns the next filename.

So what you want to do is set up an array of strings, and loop until dir("*.txt") returns NOTHING. Fill the array with the filenames, then process them in another loop.

If I get some time later I will try to help you further... Just ask.

[ammdumas]

Thanks, I'll give it a whack.

[John McGraw]

On 2002-04-23 12:24, ammdumas wrote:
Thanks, I'll give it a whack.

I forgot to mention that when using the dir("*.txt") command, you only specify the file specs the first time. To get the subsequent files you simply use dir() with no arguments.

Here is some code that fills an array with all text files. (Files_Array) It also displays a message box with each file it finds as it goes through them.

Sub get_filenames()



Dim Files_Array() As String

Dim File_Temp As String

Dim File_Count As Integer



File_Temp = Dir("*.txt")    'First time DIR is used, specify file-specs



If File_Temp = "" Then

    MsgBox "File(s) not found"

    Exit Sub

End If



File_Count = 1

ReDim Preserve Files_Array(File_Count)



Do While File_Temp <> ""

    File_Temp = Dir()           'To get the next file with the previous file

                                'specs, use dir() again, but with no arguments

    If File_Temp <> "" Then

        File_Count = File_Count + 1

        ReDim Preserve Files_Array(File_Count)

        Files_Array(File_Count) = File_Temp

        MsgBox Files_Array(File_Count) & "   Click OK for Next File"

    End If

Loop



End Sub

You can then add a simple loop to this procedure calling your sub that processes each file:

For i = 1 to File_Count

    Call My_File_Processor(Files_Array(i))

next i

Oh, and one more note. This is all done in the currently active directory. To
change the path use the command:

ChDir "Desired Path"

I hope that helped.



[ This Message was edited by: John McGraw on 2002-04-23 15:09 ]