INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

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

Jobs from Indeed

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

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!

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