String functions in VB macro

[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.

 

[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.

 

[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 ...

 

[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

 

[Dave Peterson]

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

 

[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"