InStr Function

[Boris7265]

Hello everybody .

I am adding the new sheet to existing workbook and the file that is being imported name like this 07-13-11.xls and I would like to name the new sheet as 071311. The below is code that I am able come up so far and it return the name 07-13-11.xls. I am not gof to use InStr Function , please really apreciate for help:
Sub AddWorksheet()
Dim FileName2 As String
Dim Filename1 As String
Dim FileName3 As String
Dim FileName4 As String
Dim FileName5 As String
Dim Year As String
Dim Filename As String
Dim FLName As String
FLName = Application.GetOpenFilename
Dim NewSheet As Worksheet
Filename1 = Right(FLName, 12)
FileName2 = Mid(Filename1, InStr(Filename1, "-") + 8, 12)
FileName3 = Mid(Filename1, InStr(Filename1, "-") + 1, 2)
FileName4 = Mid(Filename1, InStr(Filename1, "-") + 3, 2)
Year = "2011"
FileName5 = FileName3 + "/" + FileName4 + "/" + Year
Worksheets.Add().Name = Filename1
Set NewSheet = ActiveSheet

Thank you in advance,

Best Regards,

BorisGomel

 

[Norie]

What exactly do you want to name the worksheet?

Is it the name of the workbook the user select without the '-' and file extension?

If it is you want to strip out a specific character like '-' with this:

FLName = Replace(FLName, "-", "")

And to remove the extension:

FLName = Left(FLName, InStrRev(FLName, ".") - 1)

If used InStrRev but you could use InStr.

 

[Smitty]

Something like tHis might be less hassle:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> FLName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>        FLName = Range("A1")<br>        FLName = Left(FLName, Len(FLName) - 4)<br>        <br>        MsgBox Format(FLName, "mmddyy")<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Boris7265]

Thank you all of you for your help.

I played with codes you have provided and it is working very well .

Again , thank you very much ,

The best Regards,

orisGomel