Split string variable and use some in filename

[metropol]

I have a filname called ex: "Statusreport xxyyzz.xls"

I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.

How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.

Sorry for asking, but I have never worked with string variables before.

 

[Smitty]

You can use a formula to get the file name, then just refer to the range it's in:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

HTH,

 

[Scott Huish]

Since your leading text is always the same:

You just need the text starting at the 14th position:

Sub test()
Dim fn As String
fn = "Statusreport xxyyzz.xls"
MsgBox Mid(fn, 14)
End Sub

 

[Ragnar1211]

= mid(filename,len("Statusreport")+1,len(filename)

change filename to however you are obtaining the filename.

 

[metropol]

You can use a formula to get the file name, then just refer to the range it's in:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

HTH,

in vba code?? I'm running a macro.

 

[Scott Huish]

I gave you some VBA code, does it do what you need?

 

[metropol]

I gave you some VBA code, does it do what you need?

Maybe I don't understand this. When you use '=right ' and FIND statement, it seems to me that this is a formula you put in the excel sheet.
I'm running a macro vba code, and looking for string functions in the vba code.

I tried your code in the macro, but it did not recognize the FIND statement.

 

[Scott Huish]

That is not what I posted:

Sub test()
Dim fn As String
fn = "Statusreport xxyyzz.xls"
MsgBox Mid(fn, 14)
End Sub

 

[metropol]

That is not what I posted:

Sub test()
Dim fn As String
fn = "Statusreport xxyyzz.xls"
MsgBox Mid(fn, 14)
End Sub

So sorry Hot pepper. Didn't see your code at first.

The code you provided was a good shot. Still I have the .xls ending with is not neede Ex xxxyyyzz.xls only xxxyyyzz.

 

[Smitty]

Maybe I don't understand this. When you use '=right ' and FIND statement, it seems to me that this is a formula you put in the excel sheet.
I'm running a macro vba code, and looking for string functions in the vba code.

I tried your code in the macro, but it did not recognize the FIND statement.

I posted a formula. In your code you would then reference the cell housing the formula.

But what Scott posted works without the need for a formula.