How to trim a filename in a macro

[dktenor]

I have a macro that requests a filename and puts it in a variable "infile". I need to have the ".xls" stripped from the filename and the result put into another variable - call it "insheet".

For example:

infile = "2003-06.xls"
insheet = "2003-06"

Thanks,
doug

 

[just_jon]

Then how about --

insheet = Mid(infile, InStrRev(infile, "\") + 1, Len(infile) - InStrRev(infile, ".") + 1)

 

[dktenor]

Is it possible that I don't have the instrrev function? I'm using Excel 97 (ugh! but blame my forward-looking company)

doug

 

[just_jon]

Quite possible. Take a look at this snippet and see if it will run on '97.

Sub xx()
Dim infile As String, insheet As String, i As Integer, icount As Integer
infile = "O:\LGD Database\Extract Reformater\2003-02-obligor.xls"
icount = (Len(infile) - Len(Application.WorksheetFunction.Substitute(infile, "\", "")))
For i = 1 To icount
    infile = Mid(infile, Application.WorksheetFunction.Find("\", infile) + 1, 255)
Next i
insheet = Left(infile, Len(infile) - 4)
MsgBox insheet
End Sub

 

[dktenor]

Thanks,
In the meantime - here is what I came up with - and it works.

begin = 1
TESTSTRING = infile
test = Len(infile)
Do Until test = 0
TESTSTRING = Mid(infile, begin, Len(infile) - begin + 1)
test = InStr(TESTSTRING, "\")
begin = begin + test
Loop
insheet = Left(TESTSTRING, Len(TESTSTRING) - 4)

 

[tusharm]

In addition to the other detours you've received for the InstrRev function, you could use the code below. It provides the StrReverse and InstrRev functions in XL97 and when the workbook with the code is run on XL2000 or later, the code is automatically skipped.

#If VBA6 = 0 Then
Function StrReverse(s As String)
    Dim i As Long
    For i = Len(s) To 1 Step -1
        StrReverse = StrReverse & Mid(s, i, 1)
        Next i
    End Function
Function InstrRev(StringCheck As String, StringMatch As String, _
    Optional Start As Long = 1, Optional Compare As Long = 0)
    InstrRev = Len(StringCheck) _
        - (InStr(Start, StrReverse(StringCheck), StrReverse(StringMatch), Compare) _
        + Len(StringMatch) - 1) + 1
    End Function
    #End If

Is it possible that I don't have the instrrev function? I'm using Excel 97 (ugh! but blame my forward-looking company)

doug