Excel - Edit contents of a string

[TenDeadGoats]

Hi Guys,

Hopefully this is an easy one. I need to figure out how to trim a string to leave me with just the file name. For example:

Current in string: C:\Users\Spenc\OneDrive\Documents\Professional\Commissions Data.xslx

I want to trim down to: \Commissions Data.xslx

Anyone have any ideas? I was messing around with using ASCII codes to somehow recognize where the last "" would be and delete everything before it but I've been having some troubles getting it to work. The file name will be different every time so it needs to be able to handle that.

Thanks for any help, I really appreciate it!

[paulked]

Sub SplitString()
    Dim ar, str As String
    ar = Split("C:\Users\Spenc\OneDrive\Documents\Professional\Commissions Data.xslx", "\")
    str = "\" & ar(UBound(ar))
End Sub

[TenDeadGoats]

Hi again Paul,

That works great except for one small issue. If the file name has any spaces is it (ex. Commissions Data instead of CommissionsData) then it will only return "Data.xlsx" instead of "Commissions Data.xlsx". Is there a fix for this?

[paulked]

You obviously didn't try it it works! Try this:

Sub SplitString()
    Dim ar, str As String
    ar = Split("C:\Users\Spenc\OneDrive\Documents\Professional\Spaced out Commissions Data with extra SPACES.xslx", "\")
    str = "\" & ar(UBound(ar))
    MsgBox str
End Sub

Same code with added message to prove

[TenDeadGoats]

Hi Paul,

Sorry I should've been more clear. I am now appending this piece of code onto the piece of code I have that prompts the user to select a file. I want to have the workbook name saved in the "str" variable at the end but it's giving me some grief with the spaces still. Here is the full piece of code.

When I try to run this code choosing a file called "Commissions Data.xlsx" it only returns "Data.xlsx"

Sub Test2()
'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    'Show the dialog box
    .Show
    'Store in fullpath variable
    Dim fullpath As String
    fullpath = .SelectedItems.Item(1)
End With
Dim ar, str As String
ar = Split(fullpath)
str = "" & ar(UBound(ar))
MsgBox str
End Sub

[paulked]

Split defaults to a space if you don't give it a delimiter, where is yours in this line?

ar = Split(fullpath)

Also, you don't need to add 'nothing' to the upper bound element of the array

str = "" & ar(UBound(ar))

So the correct code is:

ar = Split(fullpath, "\")
str = ar(UBound(ar))

[TenDeadGoats]

Again, Paul, you are right on the money. That worked perfect.

I feel like a lot of the issues I am having are because I don't properly understand the syntax for these functions. Do you know of any resources I could look at for that type of thing before I come bother the smart people on this forum such as yourself?

[paulked]

You are not bothering anyone! We've all got to learn.

Using forums like tis is a great way to learn and gain experience. snb, a valued memberof this forum (and VBA Guru!), has a great site that has helped and still helps me no end. It covers all aspects of VBA and is a programmers dictionary, guide and rulebook all in one! Check it out here.