Sorting files by name(i)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ucanalways@gmail.com - 16 Apr 2008 17:14 GMT

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

Reply to this Message

Joel - 16 Apr 2008 18:01 GMT

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

> Hello group,
>
[quoted text clipped - 27 lines]
> Thanks!
> Kevin

Reply to this Message

ucanalways@gmail.com - 16 Apr 2008 18:26 GMT

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

> I assume the A is not really A but some other longer name. You need to
> extract the number from the filename.
[quoted text clipped - 44 lines]
> > Thanks!
> > Kevin

Reply to this Message

Joel - 16 Apr 2008 19:23 GMT

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

> Joel,
>
[quoted text clipped - 71 lines]
> > > Thanks!
> > > Kevin

Reply to this Message