Hi all.
I have a formula that works on a spreadsheet but char(1) isn't being recognised in VBA.
Worksheet C1 = RIGHT(C2,TRIM(FIND(CHAR(1),SUBSTITUTE(C2,"\",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2,"\","")))))+1)
Worksheet C2 = C:\Users\Name\Documents\Work\laugh GIFs.xlsm
The result in C1 = laugh GIFs.xlsm
The idea is to extract the file name by finding the Nth occurrence of "\" and then use that position to use the Right function.
So in VBA i've got..
(bare in mind C2 is now a variant called FileNm)
Any ideas??
Thanks in advance
I have a formula that works on a spreadsheet but char(1) isn't being recognised in VBA.
Worksheet C1 = RIGHT(C2,TRIM(FIND(CHAR(1),SUBSTITUTE(C2,"\",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2,"\","")))))+1)
Worksheet C2 = C:\Users\Name\Documents\Work\laugh GIFs.xlsm
The result in C1 = laugh GIFs.xlsm
The idea is to extract the file name by finding the Nth occurrence of "\" and then use that position to use the Right function.
So in VBA i've got..
(bare in mind C2 is now a variant called FileNm)
Code:
[TABLE="width: 554"]
<tbody>[TR]
[TD="class: xl63, width: 554"]Name = Right(FileNm, Trim(Application.WorksheetFunction.Find(char(1),worksheetFunction.Substitute(char(1), WorksheetFunction.Substitute(FileNm, "\", char(1), Len(FileNm) - Len(WorksheetFunction.Substitute(FileNm, "\", ""))))) + 1))[/TD]
[/TR]
</tbody>[/TABLE]
Any ideas??
Thanks in advance
Last edited: