Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Name string formatting problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Name string formatting problem
Hi
I think this is what you want: fName = UCase(Left(fName, 1) & Mid(fName, 2)) Hopes this helps. --- Per On 20 Feb., 20:52, owlnevada wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Name string formatting problem
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. "Per Jessen" wrote: Hi I think this is what you want: fName = UCase(Left(fName, 1) & Mid(fName, 2)) Hopes this helps. --- Per On 20 Feb., 20:52, owlnevada wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting string in groups of letters... | Excel Worksheet Functions | |||
formatting of numbers within a string | Excel Worksheet Functions | |||
Special string formatting | Excel Programming | |||
Number Formatting with string variables | Excel Programming | |||
Formatting Problem on Double-Zero String | Excel Programming |