Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting files by name(i)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting files by name(i)
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting files by name(i)
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 On Apr 16, 10:01 am, Joel wrote: 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 " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting files by name(i)
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 " wrote: 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 On Apr 16, 10:01 am, Joel wrote: 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 " wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
sorting in share files | Excel Discussion (Misc queries) | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Merge files & sorting data | Excel Worksheet Functions |