INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

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!

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

Strip extension from filename

(OP)
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.

Thanks

Listen to those who know, believe in those that do

RE: Strip extension from filename

(OP)
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

myFile.XYZ.123.XLS

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

Maybe something like

CODE

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 filename.123.xyz.456.abc.789.XLS

like this:

CODE

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:

CODE

whole_name = "filename.123.xyz.456.abc.789.XLS"
parts = split(whole_name, ".")
for i = 0 to ubound(parts) - 1  ' don't include exetntion
    file_name = file_name & parts(i) & "."
next  
' 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 filename.xyz.123.abc.xls

RE: Strip extension from filename

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

CODE

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

RE: Strip extension from filename

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

RE: Strip extension from filename

(OP)
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

CODE

fso.getbasename(ofile.name) 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...

CODE

On Error Resume Next

irow=2
icol=1

sfolder="d:\systest"
smaster="d:\master7.xls"

set xlapp = createobject("excel.application")

set wbtarget = xlapp.workbooks.open(smaster)
set tgtworksheet = wbtarget.worksheets("sheet1")

set fso = createobject("scripting.filesystemobject")

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

    set wbsource = xlapp.workbooks.open(ofile.path)
    
    tgtworksheet.cells(irow,icol).value=fso.getbasename(ofile.name)
    icol=icol+1

    '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)
    icol=icol+1
    
    'Get number of printers
    set srcworksheets=wbsource.worksheets(fso.getbasename(ofile.name)&"Printers")
    ttrcount= "=COUNTA(B2:B100)"
    srcworksheets.cells(7, 7).formula=ttrcount
    tgtworksheet.cells(irow,icol).value=srcworksheets.cells(7, 7)
    icol=icol+1

    '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)
    icol=icol+1

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

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

    
    icol=1
    irow=irow+1

    wbsource.close false
    
next
set fso=nothing

wbTarget.Close True

xlApp.quit

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!

Resources

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