String functions in VB macro

A

Aerojade

in C# there is a Function... String.Endswith(). How can i use this inside a
macro. i need to use it to get the extension of the file. ie...like
Filename.EndsWith("xls")

Please help.
 
J

Joel

Macros are basic language. to get the extension in basic use this

FName = "c:\temp\book1.xls"
Ext = Mid(FName,Instr(FName,".")+1)

The function MID will get the middle of the string starting at any position

InStr will return the character position of the period. The extension
starts one character after the period so I added 1 to the position.
 
R

Rick Rothstein

I don't use C#, but in looking up String.EndsWith on the internet, it
appears to return a Boolean so apparently gets used in things like If
statement tests...

If Filename.EndsWith(".xls") ...;

VBA has a Like operator which, while it has more functionality than this
example shows, can be used to duplicate the above as follows...

If Filename Like "*.xls" Then ...
 
R

Rick Rothstein

Since PC filenames can contain multiple dots in the directory name as well
as filename, your approach will fail with a path like this...

"C:\My.Temp.Dir\My.First.Book.xls"

To be sure you actually get the extension (if there is one), something like
this would be necessary...

FName = "C:\My.Temp.Dir\My.First.Book.xls"
Ext = Mid(FName, InStrRev(FName, "\") + 1)
If InStr(Ext, ".") > 0 Then
Ext = Mid(Ext, InStrRev(Ext, ".") + 1)
Else
Ext = ""
End If
 
D

Dave Peterson

You may want to look at InStrRev in VBA's help, too. (xl2k or newer only)
 
J

Joel

The problems can be simply sovled wi th Do loop

FName = "c:\temp\book1.xls"
Ext = FName
Do While Instr(Ext,".") > 0
Ext = Mid(Ext,Instr(Ext,".")+1)
Loop
 
R

Rick Rothstein

Actually, this would be simpler than that (or even what I posted earlier as
well)...

FName = "C:\My.Temp.Dir\My.First.Book.xls"
Ext = Mid(FName, InStrRev(FName, ".") + 1)

This returns the same thing as your loop; however, it does not return what
my earlier code does IF there is not dot in the filename portion of the
path, for example...

"C:\My.Temp.Dir\MyFirstBook"
 
Top