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.

Sub GetFolder() 
     
    Dim wb As Excel.Workbook 
     
    Set abc = Application.FileDialog(msoFileDialogFilePicker) 
    With abc 
        MsgBox "Select Diamond Mapping file from dialog box" 
        .Title = "Choose Files" 
        .AllowMultiSelect = False 
        If .Show <> -1 Then 
             'Exit Sub
        End If 
        FolderSelected = .SelectedItems(1) 
         
        Set wb = Workbooks.Open(Filename:=FolderSelected) 
         
    End With 
    MsgBox "You Selected:" & FolderSelected 
 
Windows("IND_abc_MAPPING20130502.csv").Activate   
End Sub

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;

Set wbMap = Workbooks.Open(Filename:=FolderSelected) 
wbMap.Activate

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

Sub GetFolder()

Dim wbDiamond As Workbook
Dim wbIndia As Workbook
Dim wbScoreFinal As Workbook
Dim wbScoreRaw As Workbook
Dim wbStatic As Workbook
Dim i As Long

Dim vTitles As Variant
Dim strFiles(0 To 4) As String

vTitles = Split("Diamond Mapping file," & _
                "INDIA Mapping file," & _
                "Score Mapping file," & _
                "Score TB Final report (Previous Month) file," & _
                "Score TB Raw file from daily listing file", ",")

For i = 0 To 4
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select " & vTitles(i)
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        strFiles(i) = .SelectedItems(1)
    End With
Next

Set wbDiamond = Workbooks.Open(strFiles(0))
Set wbIndia = Workbooks.Open(strFiles(1))
Set wbScoreFinal = Workbooks.Open(strFiles(2))
Set wbScoreRaw = Workbooks.Open(strFiles(3))
Set wbStatic = Workbooks.Open(strFiles(4))


'''''''''''''''''''''''''''''''''''''''''
'Example code to select different workbooks
wbScoreRaw.Activate
MsgBox "Score TB Raw workbook selected"

wbScoreFinal.Activate
MsgBox "Score TB Final workbook selected"
'''''''''''''''''''''''''''''''''''''''''
'
'
'
'Your code goes here

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...