VBA split string into substrings from file directory

Question

I've searched for this topic a lot but haven't found my exact issue. Also I seemingly can't figure out how to adapt the code samples for my use.

I'm trying to split a "file directory string" into substrings from right to left.

"C:\Users\Me\CustomerName\ProductName\2017\"

And split this from right to left, to

year
productname
customername

My attempts at using Split() to get it working, have always split it in the wrong places.

Update your question with the code you tried so far, as well as examples of the the unwanted behaviour/output ("...split in the wrong places.") Also: are you sure about the == part? — user7857211
– user7857211, Commented Aug 31, 2017 at 9:04

Vityata · Accepted Answer · 2017-08-31 09:02:48Z

1

You mentioned you have tried with Split, so this is a good start:

Option Explicit

Public Sub TestMe()

    Dim strFolderString As String
    Dim arrFolderString As Variant

    strFolderString = "C:\Users\Me\CustomerName\ProductName\2017\"
    arrFolderString = Split(strFolderString, "\")

    Debug.Print arrFolderString(UBound(arrFolderString) - 1)
    Debug.Print arrFolderString(UBound(arrFolderString) - 2)
    Debug.Print arrFolderString(UBound(arrFolderString) - 3)

End Sub

The idea is to use UBound as the right to left. I do not start from 0, because your string ends with \, thus the 0th position is empty.

answered Aug 31, 2017 at 9:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DMS Over a year ago

Hi, thanks that works. Your Debug.Print arrFolderString(UBound(arrFolderString) - 1) Debug.Print arrFolderString(UBound(arrFolderString) - 2) Debug.Print arrFolderString(UBound(arrFolderString) - 3) output the wrong substring though, they are all shifted one too far to the left. I've changed it to

Debug.Print arrFolderString(UBound(arrFolderString) - 0) Debug.Print arrFolderString(UBound(arrFolderString) - 1) Debug.Print arrFolderString(UBound(arrFolderString) - 2)

and now it works exactly as wanted. Thanks again! Edit: Sorry for formatting, I'm new to SO

Vityata Over a year ago

@DMS - your string ends with `\`, that's why I am using it this way. Try the code above.

DMS Over a year ago

Ah, now I see it. Apparently my real code's string didn't have the \ in the end. Now working flawlessly though, thanks for your help :)

Collectives™ on Stack Overflow

VBA split string into substrings from file directory

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related