I have a file that I create from a source document and name the file
basd on below. Which gives a new file name of "Part of name of
original file.xls 07-feb-09 1-30~.xls"

TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-
yy h-mm") & "~"

How could I exclude from the new file name the ext of the original
file? (the first.xls in my example above)

eg. what I want to appear is "Part of name of original file 07-feb-09
1-30~.xls

If the extension is always 4 characters long (.xls), then you could just avoid
them

tempfilename = "part of " & right(sourcewb.name, len(sourcewb.name)-3) & ....

But if you're using xl2007 or weird extensions, then this wouldn't work.

You could use instrrev (xl2k and above) to look for the last dot and strip the
characters based on that position. But even that assumes that the filename has
an extension.


Seanie wrote:

I have a file that I create from a source document and name the file
basd on below. Which gives a new file name of "Part of name of
original file.xls 07-feb-09 1-30~.xls"

TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-
yy h-mm") & "~"

How could I exclude from the new file name the ext of the original
file? (the first.xls in my example above)

eg. what I want to appear is "Part of name of original file 07-feb-09
1-30~.xls

--

Dave Peterson

Hi

Try this:

TempFileName = "Part of " & Left(SourceWb.Name, Len(SourceWb.Name) -
4) & " " & Format(Now, "dd-mmm- yy h-mm") & "~"

Regards,
Per

On 7 Feb., 14:06, Seanie wrote:
I have a file that I create from a source document and name the file
basd on below. Which gives a new file name of "Part of name of
original file.xls 07-feb-09 1-30~.xls"

TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-
yy h-mm") & "~"

How could I exclude from the new file name the ext of the original
file? *(the first.xls in my example above)

eg. what I want to appear is "Part of name of original file 07-feb-09
1-30~.xls

And subtract 4, not 3!

Dave Peterson wrote:

If the extension is always 4 characters long (.xls), then you could just avoid
them

tempfilename = "part of " & right(sourcewb.name, len(sourcewb.name)-3) & ....

But if you're using xl2007 or weird extensions, then this wouldn't work.

You could use instrrev (xl2k and above) to look for the last dot and strip the
characters based on that position. But even that assumes that the filename has
an extension.

Seanie wrote:

I have a file that I create from a source document and name the file
basd on below. Which gives a new file name of "Part of name of
original file.xls 07-feb-09 1-30~.xls"

TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-
yy h-mm") & "~"

How could I exclude from the new file name the ext of the original
file? (the first.xls in my example above)

eg. what I want to appear is "Part of name of original file 07-feb-09
1-30~.xls

--

Dave Peterson

--

Dave Peterson

Thanks Guys, sometimes its 3 and others 4 I could edit between both
but to keep my code standard, can I build that in? It will only ever
be xls or xlsm

Dim LastDotPos as long
dim WBName as string

wbname = wb.name
lastdotpos = instrrev(wbname,".")
if lastdotpos 0 then
wbname = left(wbname,lastdotpos-1)
end if

tempfilename = "part of " & wbname & ....

Seanie wrote:

Thanks Guys, sometimes its 3 and others 4 I could edit between both
but to keep my code standard, can I build that in? It will only ever
be xls or xlsm

--

Dave Peterson