Name a File via Code Q

  • Thread starter Thread starter Seanie
  • Start date Start date
S

Seanie

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

Try this:

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

Regards,
Per
 
And subtract 4, not 3!

Dave said:
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.
Click to expand...
 
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 & ....
 
Back
Top