File Name string formatting problem

[owlnevada]

I have a macro routine that processes files serially. When I save the file,
I want to format the name to a standard format. With these names below, I
want them to be changed with some Ucase in the first char postion when it
occurs, then allow for 1-6 digits with some times one leading 0, and text in
Lcase, then the file extension default to Lcase "xlsx".

Existing FName Desired FName

12345+.xlsx 12345+.xlsx 'no change needed
12345abs+.xlsx 12345abs+.xlsx "
12345t.xlsx 12345T.xlsx need UCase at
the 't'
v012345.xlsx V012345.xlsx need Ucase at the
'v'
v012345abs.xlsx V012345abs.xlsx "


I can get part of what I want with this:

FName=Activeworkbook.Name

FName = Lcase(Fname) 'need to account for Ucase and Lcase as above here
with something like Left(FName, Ucase,6, Ucase, 1, Right(lcase, ".")

'something in the wrong order or missing here

What syntax will it take to achieve the desired formats as on the right to
handle any of these generic variations that we run into? I don't find
anything similar in any of these posts.

Any links to advance handling of strings formatting like this would be great.

Thanks in advance.

 

[Per Jessen]

Hi

I think this is what you want:

fName = UCase(Left(fName, 1) & Mid(fName, 2))


Hopes this helps.

 

[owlnevada]

Well, not quite as in the examples I gave, the results are 12345ABS.XLSX, not
12345abs.xlsx. So it needs more specific information in this. The format as
I want it should look something like "&######&&&&&.@@@@" I think but I
can't seeem to get all the comma's and () in the right order, just get
different errors. The file name size varies. Often the t or T occurs next
to the ".", first position to the left of it following the numerics. That is
only one char position, if the letters abs or abs+. then it needs to allow
for those extra characters between the digits and the "." The "xlsx" appears
in the code window as "XLSX" but in the directory its actually lower case so
that may not be a concern.

I can't seem to find enough string handling posts or newsgroups that handle
a special format case like this, with mixed Ucase, Lcase, and the Right and
Left functions with both digits and alpha chars. Guress I'm not clear on the
argument order either.