extracting a portion of the string

[texas1014]

Hello,

I am newer to writing vba code and am having trouble in this request from my manager.
I have a text box containing a file name complete with the whole file path. It is:
"C:\Users\Me\Desktop\Project\testing database\2016\March 2016.xlsx"

I also have a prompt asking the user what they want to name the table after this file is imported as a table. How do I extract only the "March 2016", and place that into the input box so the user can just proceed if they like the file name and also have the option to edit, and I want to do this VBA? We have file naming conventions so the case of editing the file name will be rare, but it will happen.

Thanks,
Tex

 

[Gasman]

One way would be to use instrRev to find the last \ and then the right function to get the string

From the Immediate Window

str1="C:\Users\Me\Desktop\Project\testing database\2016\March 2016.xlsx"
? instrRev(str1,"\")
 50 
? right(str1,len(str1)-50)
March 2016.xlsx

You would use a variable to hold the position of the "". i just used the literal position for ease of use in the immediate window.

You could also use similar Left function to get the left part of the string to concatenate the new file name to the path.?

Use simliar logic to identify the extension
Also have a google, as I am sure I have seen a set of functions to get path, extension etc etc.

Just found this?

http://vba-tutorial.com/parsing-a-file-string-into-path-filename-and-extension/

HTH

 

[texas1014]

HTH,
Thanks. The code works to get the file name, but it is not placing it into the input box. Do you have any advice on how to get the "March 2016.xlsx" into the input box?

 

[Gasman]

HTH,
Thanks. The code works to get the file name, but it is not placing it into the input box. Do you have any advice on how to get the "March 2016.xlsx" into the input box?

You would assign the control's value to the string.
Me.FileToSave = FileNameFromPath("C:\Users\Me\Desktop\Project\testing database\2016\March 2016.xlsx") if you were to use those functions I linked to?

where FileToSave is the name of your 'input box'

BTW (By the way) HTH is an acronym for Hope This Helps.

The name is Paul, but Gasman will do.

 

[Gasman]

Sorry, I misunderstood your 'input box'. I was thinking of a textbox.

However InputBox does have the provision for a default

An example

Sub testInputBox()
Dim strInput As String, strNew As String
strInput = "Hello world"
strNew = InputBox("Enter New value", , strInput)
Debug.Print strNew
End Sub