Strip extension from filename

[SjrH]

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

[small]Listen to those who know, believe in those that do[/small]

 

[SjrH]

s'ok, sorted it

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

 

[barny2006]

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]

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

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

 

[barny2006]

you can still use split with UBOUND in whole name like filename.123.xyz.456.abc.789.XLS

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]

here's the whole 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]

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

 

[barny2006]

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

 

[PHV]

FYI: fso.getbasename(file.[!]short[/!]name)

 

[tsuji]

Not so obviously.

 

[barny2006]

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]

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\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]

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

 

[SjrH]

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(ofile.name) [i]as opposed to (ofile.shortname)[/i]

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

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



[small]Listen to those who know, believe in those that do[/small]