Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Create Import Wizard for Text Files

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location

    Create Import Wizard for Text Files

    I have a VBA macro that I want to use input as to how the text file is to be delimited before importing to EXCEL. Can anyone tell me how to enter space, comma, tab ect. as an input value in a message box.

    Thanks in advance for your help.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    How about making a UserForm with option buttons for the choices?

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    that would be fine...could you tell me how to?

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Take a look at the attachment.

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location

    Should be simple but it isn't

    Here is what I have. I have a macro that has an input box. The user enters a single character to tell the macro how to delimite the text file. I cannot get it to work. Could someone look at the code below and tell me why when entering the single character delimiter, (space, comma, tab) it will not work.
    Also, what would the user enter in the input form for a single character?


    Thanks in advance for your help. I pasted the macro below:

    [VBA]Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If

    Sub ImportTextLines()
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim FName As String
    Application.ScreenUpdating = False
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    FName = "D:\test.txt"
    Open FName For Input Access Read As #1
    While Not EOF(1)
    Line Input #1, WholeLine
    Cells(RowNdx, ColNdx).Value = WholeLine
    ColNdx = ColNdx + 1
    Wend
    Close #1
    End Sub

    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ImportTextFile CStr(FName), Sep
    End Sub[/VBA]

  6. #6
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    austenr,

    Can you provide us with the code for the "ImportTextFile" function. If you don't have the code for this function, then that is what your problem is. ImportTextFile is not a native Excel Function, but a user defined function. Let me know.
    The most difficult errors to resolve are the one's you know you didn't make.


  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Hey Austenr,

    Check out the [ VBA ] tags we have for your code. I changed your post to reflect that, it makes it easier for others to read.

  8. #8
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi austenr,

    I made an add-in for dealing with text files like this. The import portion should do what you need, and can span multiple pages if the text file is huge.
    The only reason I haven't publicized it here yet is because when the text files are over 100,000 or so lines, it takes about 2 minutes to import (too long in my opinion). But it can use multiple delimiters if desired as well. Take a look, see if it fits your needs. Put it in your
    C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\AddIns
    directory, or wherever your addin directory is located. Then go to Tools, AddIns, then check the Text Files checkbox. It adds a menu option for it automatically.
    Matt

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location

    Unhappy repost of thread below

    I was asked by someone who helped me earlier today to repost this question because I did not have all the code necessary. Hope someone can help. Thanks for all that are willing to make a suggestion.

    Here is what I have. I have a macro that has an input box. The user enters a single character to tell the macro how to delimite the text file. I cannot get it to work. Could someone look at the code below and tell me why when entering the single character delimiter, (space, comma, tab) it will not work.
    Also, what would the user enter in the input form for a single character?


    Thanks in advance for your help. I pasted the macro below:


    [VBA]Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If
    Sub ImportTextLines()
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim FName As String
    Application.ScreenUpdating = False
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    FName = "D:\test.txt"
    Open FName For Input Access Read #1
    While Not EOF(1)
    Line Input #1, WholeLine
    Cells(RowNdx, ColNdx).Value = WholeLine
    ColNdx = ColNdx + 1
    Wend
    Close #1
    End Sub
    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ImportTextFile Str(FName), Sep End Sub[/VBA]

  10. #10
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    To make it easier for everyone else, I've edited your code above to be more readable.
    *** I DID NOT MAKE ANY CHANGES TO YOUR SUBROUTINE IN THIS POST ***
    It actually looks like you put the ImportTextLines subroutine in the middle of your DoTheImport subroutine, and the DoTheImport subroutine calls ImportTextFile sub, not ImportTextLines. But either way here is your code, formatted.

    [vba]Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    Msgbox "You didn't select a file"
    Exit Sub
    End If
    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ImportTextFile CStr(FName), Sep
    End Sub
    Sub ImportTextLines()
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim FName As String
    Application.ScreenUpdating = False
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    FName = "D:\test.txt"
    Open FName For Input Access Read As #1
    While Not EOF(1)
    Line Input #1, WholeLine
    Cells(RowNdx, ColNdx).Value = WholeLine
    ColNdx = ColNdx + 1
    Wend
    Close #1
    End Sub[/vba]

  11. #11
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    OK, I changed your ImportTextLines sub to ImportTextFile. Also, I added (and commented out) a line which will restrict the Sep variable to be only 1 character long. I also changed the file number to be a variable, in the rare instance that file number 1 is already being used.
    Give these a try:

    [vba]Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    Msgbox "You didn't select a file"
    Exit Sub
    End If
    Redo:
    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ' If Len(Sep) <> 1 Then msgbox "Invalid delimiter length.": GoTo Redo
    If Sep = "" Then Exit Sub

    ImportTextfile CStr(FName), Sep
    End Sub
    Sub ImportTextfile(FName As String, Sep As String)
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim FileNum As Integer
    Application.ScreenUpdating = False
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    FileNum = FreeFile()
    Open FName For Input Access Read As #FileNum
    While Not EOF(FileNum)
    Line Input #FileNum, WholeLine
    While WholeLine Like "*" & Sep & "*"
    Cells(RowNdx, ColNdx) = Left(WholeLine, InStr(1, WholeLine, Sep, 1) - 1)
    WholeLine = Mid(WholeLine, InStr(1, WholeLine, Sep, 1) + Len(Sep))
    ColNdx = ColNdx + 1
    Wend
    Cells(RowNdx, ColNdx) = WholeLine
    RowNdx = RowNdx + 1
    ColNdx = 1
    Wend
    Close #FileNum
    End Sub[/vba]

    Matt
    Last edited by mvidas; 09-17-2004 at 10:14 AM. Reason: Changed commented line, added error checking for blank/cancel delimiter

  12. #12
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    For the record, I received a PM from austenr as follows:

    Quote Originally Posted by austenr
    So the way you rearranged the code will work? Just need to know when the box comes up to ask what you want to delimit it with what you type in and the correct format.

    ex. comma , or "comma" or ",". Currently it is putting every cell in one cell instead of breaking up the name in one, SS# in another etc.

    Thanks in advance for your help.
    and my reply:
    Quote Originally Posted by mvidas
    Hi austenr,

    In the fix I posted (my original post was just to make your code readable), you would enter "," (no quotes) in the input box.

    Also, in the message itself is a reply box at the bottom. Please put questions like this in there so people can read your responses/questions. Theres no need to add extra questions for things like this, when you can just simply reply to existing questions.

    Should you have any questions about how to use this forum, please don't hesitate to ask!
    Matt

  13. #13
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    The result is skipping cells instead of putting fields in consecutive cells in one row. Any help would be appreciated.

  14. #14
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    It shouldn't be skipping anything, could you zip up a sample text file you're using?

    What the macro does is it takes the string (ex: "qwerty,uiop,lkjhg,fd,sazx,cvb,nm"), looks for the delimiter (ex: ","), and parses it by reviewing the string
    It looks for the first instance of the delimiter (position seven in the example above), and puts 1-6 ("qwerty") in the first column. It then chops the string to after the first delimiter ("uiop,lkjhg,fd,sazx,cvb,nm"), and repeats. The only way it wouldn't work the way you want it to is if you have two delimiters in a row, it should produce a blank cell. Also, if you have the delimiter within a cell you want (if you want "last, first" to be one cell) if will divide them up as it strictly looks at the text itself.
    As I said, if you're not getting the desired result, can you please post a sample text file and tell what delimiter you want to use?

    Matt

  15. #15
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Since all these threads seem to do with the same task, I've combined them.
    ~Anne Troy

  16. #16
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    i will post a zip file tonight. i really appreciate everyones help.

  17. #17
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location

    Help again please

    Thanks to everyone who has tried to help in the past but perhaps I need to make myself more clear.

    I need a VBA macro to read a .rpt file and load it into EXCEL:

    1. Let the user select the file at time of input.

    2. Format the input file correctly so that each field goes in a separate cell not everything in one cell.

    3. Would also like to sort the file after it is imported by a column determined by the user.

    4. Print out the worksheet after it has been loaded and sorted.

    5. Close the worksheet automatically after printing.

    I know that is a lot but if someone could point me in the right direction I would be appreciative. I am really under the gun on this one.

    Here is the code I have so far:

    [VBA] Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If

    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ImportTextFile CStr(FName), Sep
    End Sub

    Sub ImportTextLines()
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim FName As String
    Application.ScreenUpdating = False
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    FName = "D:\test.txt"
    Open FName For Input Access Read As #1
    While Not EOF(1)
    Line Input #1, WholeLine
    Cells(RowNdx, ColNdx).Value = WholeLine
    ColNdx = ColNdx + 1
    Wend
    Close #1
    End Sub
    [/VBA]

  18. #18
    VBAX Regular WillR's Avatar
    Joined
    May 2004
    Location
    Rugby - UK
    Posts
    60
    Location
    Looks a bit like an attempt to mess with Chip Pearson's code....

    You're telling VBA to ImportTextFile but you do not appear to have a procedure called that...

    here is an example..

    [vba]Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String
    FName = Application.GetOpenFilename _
    (filefilter:="RPT Files(*.rpt),*.rpt,All Files (*.*),*.*")
    If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If

    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    ImportTextFile CStr(FName), Sep
    End Sub

    Public Sub ImportTextFile(FName As String, Sep As String)
    ' This code imports a "text" file
    ' It takes a full path filename and a text separator
    ' character as inputs... The macro is called from the
    ' "Do the Import" macro further down this code pane

    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim SaveColNdx As Integer

    Application.ScreenUpdating = False
    On Error GoTo EndMacro:
    SaveColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row

    Open FName For Input Access Read As #1
    'open the text file
    While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
    WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
    TempVal = Mid(WholeLine, Pos, NextPos - Pos)
    Cells(RowNdx, ColNdx).Value = TempVal
    Pos = NextPos + 1
    ColNdx = ColNdx + 1
    NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
    Wend

    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #1

    End Sub
    [/vba]

    I just tested this... I am assuming your .rpt files are from something like Query analyser ?? (Well, that's what I tested it on anyway...
    Will
    Not all chemicals are bad. Without chemicals such as hydrogen and oxygen for example, there would be no way to make water, a vital ingredient in beer.

  19. #19
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, austen. Once again--I merged the threads because they all have to do with the same issue. Creating a new thread isn't going to get us anywhere, since experts won't know what's been tried.
    ~Anne Troy

  20. #20
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    Will, When I run the code above, it abends with an ambigious compile error on the line that defines the Input text file. I didn't see any non defined names or anything. Could you please tell me why it abends? Thanks

Posting Permissions

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