Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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

SjrH (IS/IT--Management) (OP)
5 Sep 06 10:09
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

SjrH (IS/IT--Management) (OP)
5 Sep 06 10:37
s'ok, sorted it :)

(can't wait till our scripting guy gets back from holiday!)
barny2006 (MIS)
5 Sep 06 10:50
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.
Golom (Programmer)
5 Sep 06 14:21
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)
barny2006 (MIS)
5 Sep 06 14:44
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".
barny2006 (MIS)
5 Sep 06 14:49
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)
strongm (MIS)
5 Sep 06 18:09
I'm still failing to understand what the problem is with the FileSystemObject's GetBaseName function
barny2006 (MIS)
6 Sep 06 8:50
GetBaseName will probably stop at the first dot. and not get the whole file name up to the last dob.
6 Sep 06 8:53
FYI: fso.getbasename(file.shortname)
tsuji (TechnicalUser)
6 Sep 06 8:55
Not so obviously.
barny2006 (MIS)
6 Sep 06 9:26
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
barny2006 (MIS)
6 Sep 06 9:34
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.
strongm (MIS)
6 Sep 06 9:44
So, we just need some feedback from SjrH ...
SjrH (IS/IT--Management) (OP)
7 Sep 06 9:01
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

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!

Back To Forum

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