Smart questions
Smart answers
Smart people
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 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.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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

Thanks

Listen to those who know, believe in those that do

SjrH (IS/IT--Management)
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

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)
barny2006 (MIS)
5 Sep 06 14:44
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".
barny2006 (MIS)
5 Sep 06 14:49
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)
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.
PHV (MIS)
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 filename.xyz.123.abc.xls
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:

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.
strongm (MIS)
6 Sep 06 9:44
So, we just need some feedback from SjrH ...
SjrH (IS/IT--Management)
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

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!

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