Macro to open files with diffrent date format

Hi All,

Every month i have to create report using 5 diffrent files where file name remain same but only date format change.Files names are in below format

1) IND_abc_MAPPING20130502 (date format is YYYY-MM-DD)
2) IND_abc_TABLE20130502 and so on

so far code is working perfect where code ask user to select path from where he has to select files but i am stuck after that (code highlted in red) after opening all 5 files i have activate particular file and have to copy data from respective file for eg. after opening all 5 files first i have to activate 3rd file nd copy data then 5th file and so on and my problem is that every month file name will change for eg this month file name would be "IND_abc_MAPPING20130502" Next month it will be 20130602 and so on. so i need a code so that macro will activate open file using any particular word of file name because every month file name will be same only date will change.

Please Login or Register  to view this content.

Dorry for any confusion. do let me know if you need any more clarification from my side.

Thanks in advance !!!

Suyog patil

Your example code is prompting to select a file and not a folder as it uses the msoFileDialogFilePicker dialog.

When you open the file, you are assigning it to a variable. You can use that variable later in the code to reference that specific workbook regardless of its filename. Use a different variable for each file you open e.g;

Please Login or Register  to view this content.

Hi,

first of all Thanks for your quick reply. I am having one doubt code which i provided I am using that code 5 times to open 5 diffrent files because of diffrent msgbox every time. so after running this code 5 times i entered your code but that code is activating only last file. as you said i have to declare diffrent variable for each file so use "WC" instead of "WB" but it says object required..so any solution actually i am very new to VBA ....

Originally Posted by suyogpatil

Hi,

first of all Thanks for your quick reply. I am having one doubt code which i provided I am using that code 5 times to open 5 diffrent files because of diffrent msgbox every time. so after running this code 5 times i entered your code but that code is activating only last file. as you said i have to declare diffrent variable for each file so use "WC" instead of "WB" but it says object required..so any solution actually i am very new to VBA ....

Show your code with the five different file opens.

Hi please find attached file contains code which you required its my first time i am creating this so may be code is not that much perfect

Please Login or Register  to view this content.

Wohhh perfect exactly what i want .. this code is just more than perfect... really a big thanks for quick reply and taking time to solve my problem...