View Full Version : [SOLVED:] How to select just letters in the alert and also make a string
elmnas
03-03-2015, 09:15 AM
Hi guys
NewWBName = ActiveWorkbook.Name
MsgBox NewWBName
that generates this result
http://i.imgur.com/Xds4ARI.png
How do I just select
"_no"
and make a string?
COuld someone help me ?
Thank you in advance
Blade Hunter
03-03-2015, 02:08 PM
This should work:
Sub elmnas()
Dim MyString As String 'Dimension as a string
MyString = ActiveWorkbook.Name 'Set the String = workbook name
MyString = Mid(MyString, InStr(1, MyString, "_")) ' Remove part of string up to _
MyString = Replace(MyString, ".xls", "") 'Remove .xls
MsgBox MyString 'Display string
End Sub
If you are comfortable with what it is doing you can put both the string manipulation lines on one line like this:
MyString = Replace(Mid(MyString, InStr(1, MyString, "_")), ".xls", "")
And then if you are comfortable with that you can combine the initial MyString line like this:
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
the entire routine then gets down to this:
Sub elmnas()
Dim MyString As String
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
MsgBox MyString
End Sub
elmnas
03-04-2015, 03:51 AM
This should work:
Sub elmnas()
Dim MyString As String 'Dimension as a string
MyString = ActiveWorkbook.Name 'Set the String = workbook name
MyString = Mid(MyString, InStr(1, MyString, "_")) ' Remove part of string up to _
MyString = Replace(MyString, ".xls", "") 'Remove .xls
MsgBox MyString 'Display string
End Sub
If you are comfortable with what it is doing you can put both the string manipulation lines on one line like this:
MyString = Replace(Mid(MyString, InStr(1, MyString, "_")), ".xls", "")
And then if you are comfortable with that you can combine the initial MyString line like this:
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
the entire routine then gets down to this:
Sub elmnas()
Dim MyString As String
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
MsgBox MyString
End Sub
I dont make it work why?
I get this error
12961
Blade Hunter
03-04-2015, 02:29 PM
There could be a bunch of reasons, most likely the name doesn't have an underscore in it. What is the name of the sheet you are trying to use?
elmnas
03-06-2015, 08:21 AM
Thank you solved :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.