CODEWITHSUNDEEP
vbaexcel
senzacionale
senzacionale

Reputation: 20916

Getfolder order by file name

This is my VB code for merging XML but I would like to merge it by file name. So first 1.xsl then 2.xsl

How to order a list in VB?

Sub simpleXlsMerger()

Dim bookList As Workbook

Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

Application.ScreenUpdating = False

Set mergeObj = CreateObject("Scripting.FileSystemObject")



'change folder path of excel files here

Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")

Set filesObj = dirObj.Files

For Each everyObj In filesObj

Set bookList = Workbooks.Open(everyObj)



'change "A2" with cell reference of start point for every files here

'for example "B3:IV" to merge all files start from columns B and rows 3

'If you're files using more than IV column, change it to the latest column

'Also change "A" column on "A65536" to the same column as start point

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

ThisWorkbook.Worksheets(1).Activate



'Do not change the following column. It's not the same column as above

Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Application.CutCopyMode = False

bookList.Close

Next

End Sub

Upvotes: 1

Views: 4565

Answers (2)

tbur
tbur

Reputation: 2454

There is a really, really fast way to get an array of sorted files from a directory.

Sub SortDirectoryAscending()

    Dim r() As String

    r = Filter(Split(CreateObject("wscript.shell").exec _
                 ("cmd /c Dir ""C:\test\"" /b /a-d /on") _
                 .stdout.readall, vbCrLf), ".")

   For Each file_ In r
     MsgBox (file_)
   Next file_

End Sub

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

@AFischbein is correct that there is no built-in sort for collections, arrays, dictionaries, etc. in VBA. However, I recently learned about the System.Collections.ArrayList which does have a built-in sort method, which would work for 1-dimensional arrays.

You can try for yourself with this example:

Sub test()
Dim list As Object
Dim i as Integer
Dim arr As Variant

arr = Array(99, 25, 37, 3, 29, 33, 4, 105, 1)

Set list = CreateObject("System.Collections.Arraylist")

For i = LBound(arr) To UBound(arr)
    list.Add arr(i)
Next

list.Sort

arr = list.ToArray()
End Sub

In your example, maybe something like (untested):

Dim list As Object
Dim listItem as Variant
Dim bookList as Workbook
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

Application.ScreenUpdating = False

Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here

Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")

Set filesObj = dirObj.Files

Set list = CreateObject("System.Collections.Arraylist")
'## Store the names in an ArrayList
For Each everyObj In filesObj
    list.Add everyObj
Next

'## Sort the list (ascending)
list.Sort
'## Optionally, sort it descending:
'list.Reverse

For each listItem in list.ToArray()
    Set bookList = Workbooks.Open(listItem)

   '## The rest of your code goes here...
    bookList.ActiveSheet.Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

    ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

    Application.CutCopyMode = False

    bookList.Close
   '
Next

Upvotes: 0

Related Questions