HELP, HELP !!! formating problem in macro URGENT !

[andree.seidel]

Hi,

the following line:

SaveName = Left(CSVName, Len(CSVName) - 3) & "xls"

in my macro gives me the following value for SaveName:

C:\Temp\Excel_in\process.xls


CSVName is C:\Temp\Excel_in\process.csv

Does anybody know how the line should look like to get the following value for SaveName:

process.xls

Thanks in advance

Andree

 

[Norie]

Andree

What version of Excel do you have?

There is an InStrRev function in later versions which you could use like this:

CSVName = "C:\Temp\Excel_in\process.csv"
SaveName = Mid(CSVName, InStrRev(CSVName, "\") + 1)
SaveName = Left(SaveName, Len(SaveName) - 3) & "xls"

If your version doesn't have this function you could use this:

Public Function InStrRev(SearchText As String, FindText As String) As Long
Dim RevSearchText As String
Dim LengthSearchText As Long
Dim I%
    LengthSearchText = Len(SearchText)
    
    For I = LengthSearchText To 1 Step -1
        RevSearchText = RevSearchText & Mid(SearchText, I, 1)
    Next I
    InStrRev = InStr(RevSearchText, FindText)
    InStrRev = LengthSearchText - InStrRev + 1

End Function

I wrote this when I was using Excel 97.

 

[erik.van.geit]

andree.seidel,

Something like this could help ?

Kind regards,
Erik

Sub find_filename()
'get name out of string
Dim i As Integer, length As Integer, findname As String, wfn As String
wfn = ThisWorkbook.FullName
length = Len(wfn)
findname = ""
  For i = length To 1 Step -1
  If Mid(wfn, i, 1) = Application.PathSeparator Then Exit For
  findname = Mid(wfn, i, 1) & findname
  Next i
MsgBox findname
End Sub

 

[andree.seidel]

Hi,

thanks for the fast reply, I used the following solution:

CSVName = "C:\Temp\Excel_in\process.csv"
SaveName = Mid(CSVName, InStrRev(CSVName, "\") + 1)
SaveName = Left(SaveName, Len(SaveName) - 3) & "xls"

That works perfect

Could you explain me the meanig of:

SaveName = Mid(CSVName, InStrRev(CSVName, "\") + 1)

in words ? I would like to understand this macro language.

Thanks for the fast reply/help/solution.

See you

Andree

 

[Zack Barresse]

It's basically like the MID Excel worksheet function:

MID ( String to check , Start character , End character )

The InStrRev VBA method will go through a string from the end (far right) and look to the left for the character specified, finding the instance specified (first if omitted or left blank). This is utilized because of the many possibilities of sub-folders and the amount of path seperators ( \ ) possible. The file name will be after the last one, or the first one found coming from the right.

So it becomes:

MID("C:\Temp\Excel_in\process.csv",18)

In other words, it will only bring back the Name of the file from the entire path.

Edit: Note the version was asked by Norie because the InStrRev function was not available in Excel 97 or earlier.


HTH

 

[andree.seidel]

Hi,

OK I got it, InStrRev gives me the position of the first found \, by start searching from the right end, means 17.

My Excel version is 2002.

Thanks a lot

Andree
[/b]