With the code below I get a range of cells from different files and pu
it in a new file named globalbook

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls)
*.xls", _
MultiSelect:=True)
...
For... ...
...
globalbook.Worksheets("X").Cells(r, 1).Value = insertbook.Name
r+1
...


The last line writes the name of each file on sheet X

My problem is that I don't need the *.xls * to be written on shee
X...*how can I easily trim the .xls from the filename?

--
Zur
-----------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...fo&userid=1464
View this thread: http://www.excelforum.com/showthread.php?threadid=26314

Zurn,

Replace

insertbook.Name

with

Replace(insertbook.Name,".xls,"")

HTH,
Bernie
MS Excel MVP

"Zurn" wrote in message
...

With the code below I get a range of cells from different files and put
it in a new file named globalbook

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
..
For... ...
..
globalbook.Worksheets("X").Cells(r, 1).Value = insertbook.Name
r+1
..


The last line writes the name of each file on sheet X

My problem is that I don't need the *.xls * to be written on sheet
X...*how can I easily trim the .xls from the filename?*


--
Zurn
------------------------------------------------------------------------
Zurn's Profile:
http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=263149

globalbook.Worksheets("X").Cells(r, 1).Value =
Left(insertbook.Name,len(insertBook.Name)-4)

or

globalbook.Worksheets("X").Cells(r, 1).Value
=Application.Substitute(insertBook.Name,".xls","")

or use the VBA replace function in xl2000 and later instead of
Application.Substitute.

--
Regards,
Tom Ogilvy

"Zurn" wrote in message
...

With the code below I get a range of cells from different files and put
it in a new file named globalbook

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
..
For... ...
..
globalbook.Worksheets("X").Cells(r, 1).Value = insertbook.Name
r+1
..


The last line writes the name of each file on sheet X

My problem is that I don't need the *.xls * to be written on sheet
X...*how can I easily trim the .xls from the filename?*


--
Zurn
------------------------------------------------------------------------
Zurn's Profile:
http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=263149

typo:

Replace(insertbook.Name,".xls,"")

should be


Replace(insertbook.Name,".xls","")

Note that the replace command was added in xl2000.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zurn,

Replace

insertbook.Name

with

Replace(insertbook.Name,".xls,"")

HTH,
Bernie
MS Excel MVP

"Zurn" wrote in message
...

With the code below I get a range of cells from different files and put
it in a new file named globalbook

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
..
For... ...
..
globalbook.Worksheets("X").Cells(r, 1).Value = insertbook.Name
r+1
..


The last line writes the name of each file on sheet X

My problem is that I don't need the *.xls * to be written on sheet
X...*how can I easily trim the .xls from the filename?*


--
Zurn
------------------------------------------------------------------------
Zurn's Profile:
http://www.excelforum.com/member.php...o&userid=14645
View this thread:
http://www.excelforum.com/showthread...hreadid=263149