Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2013
    Posts
    7

    Unanswered: split function in vba does not work as expected

    Hi all,
    I have a text box control on a form that get populated with file path such as :

    C:\Users\babarnc\Documents\test.xls
    C:\Users\babarnc\Documents\test2.xls
    C:\Users\babarnc\Documents\test3.xls
    I need to store in a table each file path, file name and extension.

    So first step for me is to store each line of the textbox into a sting array:

    Code:
    Private Sub Cmd_OK_Click()
        Dim FullNameToFile() As String
        Dim OldPath As String
        Dim i As Long
        Dim nofFiles As Long
        
        FullNameToFile() = Split(Me.Txt_File, vbLf)
        nofFiles = UBound(FullNameToFile) - LBound(FullNameToFile) + 1
        
        For i = 0 To nofFiles - 1
            OldPath = OldPath & FullNameToFile(i) & "+"
        Next
        MsgBox OldPath
    End Sub

    and here is what I get in my message box:

    C \Users\babarnc\Documents\test xls+C \Users\babarnc\Documents\test2 xls+C \Users\babarnc\Documents\test3 xls++
    Where has all my punctuation gone???
    Now without ":" or "." my path are useless, and there is no way to rebuilt them as some file or folder name may well have spaces

    I have tried to find help on internet but as soon as you type split and punctuation you get load of example on how to get ride of the punctuation, and nothing about how to keep it

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    with this:
    Code:
    Split(Me.Txt_File, vbLf)
    You try to split the text value of Txt_File using the Line Feed character (chr10) as separator.
    If you want to split the string with:
    Code:
    FullNameToFile(0) = Path
    FullNameToFile(1) = Name
    FullNameToFile(2) = Extension
    you can use:
    Code:
        Dim FullNameToFile(0 To 2) As String
        
        FullNameToFile(0) = Left(Me.Txt_File.Value, InStrRev(Me.Txt_File.Value, "\"))   ' Path
        FullNameToFile(2) = Mid(Me.Txt_File.Value, InStr(Me.Txt_File.Value, ".") + 1)  ' Extension
        FullNameToFile(1) = Replace(Mid(Me.Txt_File.Value, Len(FullNameToFile(0)) + 1), "." & FullNameToFile(2), "") ' Name
    Have a nice day!

  3. #3
    Join Date
    Aug 2013
    Posts
    7
    Hi Sinndho,
    First, thanks for the help. But actually, my issue is different.
    I wasn't clear.

    The user selects multiple file and this populate a textbox with a string like:

    C:\Users\babarnc\Documents\test.xls
    C:\Users\babarnc\Documents\test2.xls
    C:\Users\babarnc\Documents\test3.xls
    The quote above represent a single string.
    So I am trying to put each line in one entry of my array.
    what I am trying to get is :

    FullNameToFile(0) = C:\Users\babarnc\Documents\test.xls
    FullNameToFile(1) = C:\Users\babarnc\Documents\test2.xls
    FullNameToFile(2) = C:\Users\babarnc\Documents\test3.xls
    and what I get is

    FullNameToFile(0) = C \Users\babarnc\Documents\test xls
    FullNameToFile(1) = C \Users\babarnc\Documents\test2 xls
    FullNameToFile(2) = C \Users\babarnc\Documents\test3 xls
    which is not fine because some of the file I will have will be like

    C:\myfolder\my file.1.2.txt

    or C:\my file.blabla.txt

    so I suppose I still could parse them but

    1.this annoying
    2. I am curious and want to learn, why this is happening

    Ps I made a mistake I actually do

    Code:
    Split(Me.Txt_File, vbCrLf)
    because the " All path " string separator is vbCrLf by construction.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Now I understand better. Try:
    Code:
    Private Sub Cmd_OK_Click()
    
        Dim FullNameToFile As Variant
        
        FullNameToFile = Split(Me.Txt_File.Value, vbNewLine)
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You stated
    The user selects multiple file and this populate a textbox with a string
    Is it possible to capture the selected file and store it in a temp table or an array etc. before the text box is populated?

  6. #6
    Join Date
    Aug 2013
    Posts
    7
    Thank you Sinndho, I will try it
    And Yes, Poppa Smurf, this is what I am doing now, it removes one step, and work straight away, but it is still nice to understand what was going wrong.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Aug 2013
    Posts
    7
    By the way I used your code:

    Code:
    FullNameToFile(0) = Left(Me.Txt_File.Value, InStrRev(Me.Txt_File.Value, "\"))   ' Path
        FullNameToFile(2) = Mid(Me.Txt_File.Value, InStr(Me.Txt_File.Value, ".") + 1)  ' Extension
        FullNameToFile(1) = Replace(Mid(Me.Txt_File.Value, Len(FullNameToFile(0)) + 1), "." & FullNameToFile(2), "") ' Name
    to parse my file path, it was great as I did not have to think how to code it.

    I just changed

    Code:
      FullNameToFile(2) = Mid(Me.Txt_File.Value, InStr(Me.Txt_File.Value, ".") + 1)  ' Extension
    with
    Code:
      FullNameToFile(2) = Mid(Me.Txt_File.Value, InStrRev(Me.Txt_File.Value, ".") + 1)  ' Extension
    to make sure it still work even if the file names are :blabla.blabla.blabla.txt

    regards

Posting Permissions

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