Mid function
 

Hi,

In vba I want to get a part of the workbooks' name in a string.

Each workbook has a name like this

test_XX_YYY.xls
or only test_XX.xls

I want everything before .xls and after the first underscore sign. For
the above filenames I would like to have:

XX_YYY
and
XX

Mid function
 

On Thu, 1 Oct 2009 05:45:54 -0700 (PDT), Ixtreme wrote:

Hi,

In vba I want to get a part of the workbooks' name in a string.

Each workbook has a name like this

test_XX_YYY.xls
or only test_XX.xls

I want everything before .xls and after the first underscore sign. For
the above filenames I would like to have:

XX_YYY
and
XX


In VBA:

Mid(ThisWorkbook.Name, InStr(ThisWorkbook.Name, "_") + 1)


--ron

Mid function
 

Try

newVar = Mid$(var, InStr(var, "_") + 1, InStrRev(var, ".") - InStr(var,
"_") - 1)

--
__________________________________
HTH

Bob

"Ixtreme" wrote in message
...
Hi,

In vba I want to get a part of the workbooks' name in a string.

Each workbook has a name like this

test_XX_YYY.xls
or only test_XX.xls

I want everything before .xls and after the first underscore sign. For
the above filenames I would like to have:

XX_YYY
and
XX

Mid function
 

Hi

Look at this example:

Sub aaa()
MyString = "test_XX_YYY.xls"
MyString = Right(MyString, Len(MyString) - 5)
LastChar = WorksheetFunction.Find(".", MyString) - 1
Result = Left(MyString, LastChar)
Debug.Print Result
End Sub

Regards,
Per

"Ixtreme" skrev i meddelelsen
...
Hi,

In vba I want to get a part of the workbooks' name in a string.

Each workbook has a name like this

test_XX_YYY.xls
or only test_XX.xls

I want everything before .xls and after the first underscore sign. For
the above filenames I would like to have:

XX_YYY
and
XX

Mid function
 

try the following:-

strWkBKName = ThisWorkbook.Name
strResult = Mid(strWkBKName, InStr(1, strWkBKName, "_") + 1,
Len(strWkBKName) - 4 - InStr(1, strWkBKName, "_"))

"Ixtreme" wrote in message
...
Hi,

In vba I want to get a part of the workbooks' name in a string.

Each workbook has a name like this

test_XX_YYY.xls
or only test_XX.xls

I want everything before .xls and after the first underscore sign. For
the above filenames I would like to have:

XX_YYY
and
XX