Sorting files by name(i)

[ucanalways]

Hello group,

I am using the Application.filesearch and the following code

If .execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
x = .FoundFiles.Count

i = 0
For i = 1 To x
Dim ActivePath As String
ActivePath = .FoundFiles(i)
Workbooks.Open (ActivePath)

'Then I am doing a Save As i_filename
Next i
.....

Example:
I have 40 files named A1,A2,A3.....A10, A11,A12...A21,A23,A24.....A40.
Files should be saved in the format: i_Ai
i.e. 1_A1, 2_A2, 3_A3, 4_A4........ 40_A40

Instead I have the files saved in the following format 1_A1, 2_A10,
3_A11, 4_A12 etc..

Please let me know a way to save the files in the way I intend to
save.

Thanks!
Kevin

 

[Joel]

I assume the A is not really A but some other longer name. You need to
extract the number from the filename.

BaseName = "ABC"
Extension = ".XLS"
For i = 1 To x
Dim ActivePath As String
ActivePath = .FoundFiles(i)
Workbooks.Open (ActivePath)
bkname = ActiveWorkbook.Name
'remove extension
bkname = Left(bkname, InStr(bkname, ".") - 1)
'extract number
filenumber = Mid(bkname, Len(BaseName) + 1)
NewName = filenumber & "_" & BaseName & filenumber & extension

 

[ucanalways]

Joel,

I ran a test case with your code.

Dim bkname As String
BaseName = "ABC"
Extension = ".XLS"

bkname = ActiveWorkbook.Name
'remove extension
bkname = Left(bkname, InStr(bkname, ".") - 1)
'extract number
filenumber = Mid(bkname, Len(BaseName) + 1)
newname = filenumber & "_" & BaseName & filenumber &
Extension
MsgBox newname

The name of the workbook is test.xls in this case.

I get t_ABCt.xls as the result in msgbox and I don't get any number.
Any idea what's goin on?

BTW, what do you refer basename as? "ABC" is that something constant
this set of code, just to get "len" out of it?

Kevin

 

[Joel]

I needed to extract the number from the filename without any extension. This
was the easiest way for accomplishing tthis operation. the other method is
to check each character until you find a numeric character. The problem with
looking for a numberic character is if the base file name has a number it
doesn't work