Name a File via Code Q
 

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

Name a File via Code Q
 

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

Name a File via Code Q
 

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

Name a File via Code Q
 

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

Name a File via Code Q
 

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

Name a File via Code Q
 

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