Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Strip extension from filename

Strip extension from filename

I have a script that collects a list of files within a folder, then places each filename on a new row in excel.

How can I get rid of the file extension when writing the filename to excel?

I've tried using fso.getbasename(file.shortname) method, but some of these files are much longer than 8 characters. As they identify computer names, I need to be able to see the entire filename before the extension.


Listen to those who know, believe in those that do

RE: Strip extension from filename

s'ok, sorted it :)

(can't wait till our scripting guy gets back from holiday!)

RE: Strip extension from filename

use split command to split file name and extention:
parts = split(whole_name, ".")
file_name = parts(1)
ext = parts(2)

now, you can put file_name in excel.

RE: Strip extension from filename

Since things like


are legal file names you will probably need to be a bit more elaborate of you use "Split(whole_name,".")"

Maybe something like


File_Name = Left(Whole_Name, InStrRev(Whole_Name,".") - 1)
Ext       = Mid (Whole_Name, InStrRev(Whole_Name,".") + 1)

RE: Strip extension from filename

you can still use split with UBOUND in whole name like

like this:


for i = 0 to ubound(parts)

in this case, split will give you 7 entries. the last one being extention and the rest being the "filename".

RE: Strip extension from filename

here's the whole code:


whole_name = ""
parts = split(whole_name, ".")
for i = 0 to ubound(parts) - 1  ' don't include exetntion
    file_name = file_name & parts(i) & "."
' get rid of the dot in len - 1
file_name = mid(file_name,1, len(file_name)-1)

RE: Strip extension from filename

I'm still failing to understand what the problem is with the FileSystemObject's GetBaseName function

RE: Strip extension from filename

GetBaseName will probably stop at the first dot. and not get the whole file name up to the last dob.

RE: Strip extension from filename

FYI: fso.getbasename(file.shortname)

RE: Strip extension from filename

Not so obviously.

RE: Strip extension from filename

all the examples i've seen at msn, points to files with a single dot. file.txt, however, i have not seen any examples of files with names like

RE: Strip extension from filename

i stand corrected.
getbasename will get you up to the last dot.
i tested it.
here's the code:


dim fso, path
path = "c:\a_a\"
set fso = createobject("scripting.filesystemobject")
getbase = fso.getbasename(path)
msgbox getbase
will result in:
which is what you want.

RE: Strip extension from filename

So, we just need some feedback from SjrH ...

RE: Strip extension from filename

Thanks guys :)

I stumbled upon the answer 10 or so mins after I first posted.

As PHV states above, all I need to do was simply use


fso.getbasename( as opposed to (ofile.shortname)

I'm sure all the other suggestions are just as viable to!

So this is what I used to pull selected data from 4 worksheets in 5000+ files, and write it into one...


On Error Resume Next



set xlapp = createobject("excel.application")

set wbtarget =
set tgtworksheet = wbtarget.worksheets("sheet1")

set fso = createobject("scripting.filesystemobject")

for each ofile in fso.getfolder(sfolder).files

    set wbsource =

    'Get number of Apps
    set srcworksheets = wbsource.worksheets("Applications")    
    strcount= "=COUNTA(A1:A200)"
    srcworksheets.cells(5, 5).formula=strcount    
    tgtworksheet.cells(irow,icol).value=srcworksheets.cells(5, 5)
    'Get number of printers
    set srcworksheets=wbsource.worksheets(fso.getbasename("Printers")
    ttrcount= "=COUNTA(B2:B100)"
    srcworksheets.cells(7, 7).formula=ttrcount
    tgtworksheet.cells(irow,icol).value=srcworksheets.cells(7, 7)

    'Get number of profiles
    set srcworksheets=wbsource.worksheets("profiles")
    utrcount= "=COUNTA(A1:A200)"
    srcworksheets.cells(8, 8).formula=utrcount
    tgtworksheet.cells(irow,icol).value=srcworksheets.cells(8, 8)

    'Get last log on
    tgtworksheet.cells(irow,icol).value=srcworksheets.cells(1, 2)

    'get IP address
    set srcworksheets=wbsource.worksheets("summary")
    set srcCell = srcWorksheets.Cells(2, 4)
    if srcCell="" then Set srcCell = srcWorkSheets.Cells(7, 4)


    wbsource.close false
set fso=nothing

wbTarget.Close True


set xlApp=nothing

Msgbox "Done"

Huge thanks to Tsuji for the script samples submitted in my other thread, some of which are used here :)

I knew absolutely nothing about scripting a week or so ago, i'll certainly be using it a lot more now!

Now on to getting it all into access, oh joy :(

Listen to those who know, believe in those that do

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close