Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Importing Text Files Using Macros

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Waterdown, Ontario
    Posts
    464
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Waterdown, Ontario
    Posts
    464
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, I'll give it a whack.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •