Open multiple files from folder

[vinmam123VBA]

Hi Team,

Can any one please help me with the VBA code for below-Your help is very much appreciated

I have multiple source files on weekly basis. I need to open all files at once from the folder particular folder.( I need to select the folder where I saved these files by browsing)

Then I need to switch between the open files to perform different tasks and then save the files in different folder( I need to select this folder by browsing) and close.

For example I need to copy data from " Source1_Week 29.xls" then how I will select " Source1_Week 29.xls" file. After that I want edit dates in " Source2_Week 29.xls". How to select it.

Please be informed that these file names keep changing every week. Also Number of files also changes.(Four example some week I have 2 files and some week I will be having four files)

For example below are the file name(Names keep changing every week)
Source1_Week 29.xls
Source2_Week 29.xls
Source3_Week 29.xls
Source4_Week 29.xls

Regards,
Vinmam

 

[rollis13]

How about starting by dividing the problem into many small sub-problems. Then you can proceed alone one-by-one.
For the first sub-problem:

I have multiple source files on weekly basis. I need to open all files at once from the folder particular folder.( I need to select the folder where I saved these files by browsing)

Here is a macro that does exactly what you are asking for (link to source):

Option Explicit
Sub Open_all_excel_files_in_folder()
    Dim FoldPath As String
    Dim DialogBox As FileDialog
    Dim FileOpen As String
    On Error Resume Next
    Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker)
    If DialogBox.Show = -1 Then
        FoldPath = DialogBox.SelectedItems(1)
    End If
    If FoldPath = "" Then Exit Sub
    FileOpen = Dir(FoldPath & "\*.xls*")          '<- here change file extension if necessary
    Do While FileOpen <> ""
        Workbooks.Open FoldPath & "\" & FileOpen
        FileOpen = Dir
    Loop
End Sub

 

[vinmam123VBA]

How about starting by dividing the problem into many small sub-problems. Then you can proceed alone one-by-one.
For the first sub-problem:

Here is a macro that does exactly what you are asking for (link to source):

Option Explicit
Sub Open_all_excel_files_in_folder()
    Dim FoldPath As String
    Dim DialogBox As FileDialog
    Dim FileOpen As String
    On Error Resume Next
    Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker)
    If DialogBox.Show = -1 Then
        FoldPath = DialogBox.SelectedItems(1)
    End If
    If FoldPath = "" Then Exit Sub
    FileOpen = Dir(FoldPath & "\*.xls*")          '<- here change file extension if necessary
    Do While FileOpen <> ""
        Workbooks.Open FoldPath & "\" & FileOpen
        FileOpen = Dir
    Loop
End Sub

Hi rollis13,

It works great!

Going further- once file are open , if I want to activate file, for example "Source2_Week 29.xls" (this file name keeps changing) and make some changes(As per the my internal work process) and then

I want to activate "Source3_Week 29.xls" (this file name keeps changing) and make some changes(As per the my internal work process) and Then

I want to activate file, for example "Source4_Week 29.xls" (this file name keeps changing) and make some changes(As per the my internal work process) and then

I want to activate file, for example "Source1_Week 29.xls" (this file name keeps changing) and make some changes(As per the my internal work process) and then

I want to activate file, "Source4_Week 29.xls" (this file name keeps changing) again and make some changes(As per the my internal work process).

So what I am looking for is once files are open they should have some Variable names (Source3_Week 29.xls - FiletoOpen3,Source2_Week 29.xls - FiletoOpen2 and so on)

For this what I am looking for is-

In frontend(May be by user form) I should have option to select four files-

Browse file one (Here I will select Source1_Week 29.xls and for this file selected name should be something "FileToOpen1" so that in further code I can activate this file with the name FileToOpen1).
Browse file Two (Here I will select Source2_Week 29.xls and for this file selected name should be something "FileToOpen2" so that in further code I can activate this file with the name FileToOpen2).
Browse file Three (Here I will select Source3_Week 29.xls and for this file selected name should be something "FileToOpen3" so that in further code I can activate this file with the name FileToOpen3).
Browse file Four (Here I will select Source4_Week 29.xls and for this file selected name should be something "FileToOpen4" so that in further code I can activate this file with the name FileToOpen4).

Note - I have seen this kind of Macro (as end user) earlier in my career however I am not sure about the code

Regards,
Vin

 

[rollis13]

Wouldn't it be more simple to recall the required workbook from the Application TaskBar ?

 

[vinmam123VBA]

Wouldn't it be more simple to recall the required workbook from the Application TaskBar ?

Thank you for the response!

Can you please help me how is that, I think I never come across

Regards,
Vin

 

[rollis13]

This would be my Application TaskBar.
Click here:

 

[vinmam123VBA]

Any Help on this is much appreciated!

Regards,
Vinayak