VBA - open all workbooks in a specific order

[biubiubiu]

Hi Guys,

I have a bunch of excel workbooks with names like these :

BOCOM_HK_719286_06260329_P1
BOCOM_HK_719286_06260329_P2
BOCOM_HK_719286_06260329_P3
...
BOCOM_HK_719286_06260329_P11


I need to open these files in the ascending numeric order (p1,p2,p3,p4 etc.)

however, currently the opening order is:
BOCOM_HK_719286_06260329_P1
BOCOM_HK_719286_06260329_P10
BOCOM_HK_719286_06260329_P2
BOCOM_HK_719286_06260329_P20
BOCOM_HK_719286_06260329_P3
BOCOM_HK_719286_06260329_P4
...

The code seems to priortize p10 over p2 and p20 over p3 etc.

What changes should I make to acheived the desired results?

Thanks.

The related section of my code is as follows:

Sub BOCOM()

Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook

MyFolder = "C:\Users\" & user & "\Desktop\New folder"
MyFile = Dir(MyFolder & "\*.csv")
Do While MyFile <> ""
Set wb = Workbooks.Open(filename:=MyFolder & "\" & MyFile)
wb.Close savechanges:=False

MyFile = Dir
Loop

 

[biubiubiu]

Hi WBD
I cant seem to reproduce the same results from my end, even if I set up the files in the folder with exactly the same name (the debug print still shows it is opening p10 before p2).

I guess I will leave this for now and revisit this problem after I gain more VBA coding knowledge (currently on page63 of <VBA and MACROs Excel 2013> ).

Thanks for all your help.

 

[biubiubiu]

Hi Daverunt

This works perfectly for my case, since each folder has only a maximum of around 30 files.

All I need to do is add a few more line of code to define a new variable, which represents the string "BOCOM_HK_719286_06260329_P" because it will be always be different.


Thanks for the help

 

[daverunt]

Glad it worked for you.