macro to pick data from various files

Hello...Can someone help me in writing a macro which fulfils my requirement. I have an excel file contains around 10 worksheets named a,b,c and so on...... In "C" drive there is a folder where 10 workbooks (say 1, 2, 3 and so on) are stored. Every workbook there is a sheet named "price". format of price sheet in every file is same. Price sheet contains column A to Y. Workbook which has 10 worksheets is associated with 10 workbooks in such a way that worksheet A and workbook 1, worksheet B and workbook 2 and so on, are to be connected with each other. Now the requirement is that macro to be written in a file which contains 10 worksheets so that each worksheet picks data from "price" worksheet of its connected workbook (folder in c drive which contains 10 workbook). It is to be noted that only below mentioned data to be picked :- R2C3, R10C3, R12C3, R2C6, R10C6, R12C6, R2C10, R2C10, R2C10

Pls help if someone has any idea.

Your suggestion and desired action would be highly appreciated.

RE: Macro to pick data from various files

Hi,

If you still need help, here's some solution.

Note: In your post you write: "... R2C3, R10C3, R12C3, R2C6, R10C6, R12C6, R2C10, R2C10, R2C10...". I suppose you mean: R2C3, R10C3, R12C3, R2C6, R10C6, R12C6, R2C10, R10C10, R12C10 and my example is on that assumption but it is realy easy to make the necessary changes into the code.

Put the following code into the standard code module in your main file:

' ************************* ' ************************* '

' Change the following with your real file name
Public Const FILE_NAMES As String = _
    "1.xlsx;2.xlsx;3.xlsx;4.xlsx;5.xlsx;6.xlsx;7.xlsx;8.xlsx;9.xlsx;10.xlsx"
' Change the following with your real worksheet name
Public Const WSH_NAMES As String = _
    "a;b;c;d;e;f;g;h;i;j"
' Change the following if necessary
Public Const PRICE_WSH As String = "Price"
' Change the following if you want another copy ranges
Public Const RNG_ADDRESS As String = _
    "C2;C10;C12;F2;F10;F12;J2;J10;J12"

Sub PickDataFromVariousFiles()

    Dim oFileDialog As FileDialog
    Dim sFolderPath As String
    Dim aFileNames() As String
    Dim aWshNames() As String
    Dim aRngAddress() As String
    Dim oMainWbk As Workbook
    Dim oCurrentWbk As Workbook
    Dim oMainWsh As Worksheet
    Dim oCurrentWsh As Worksheet
    Dim i As Integer
    Dim j As Integer

   
    Application.ScreenUpdating = False
   
    On Error GoTo ERROR_HANDLER
   
    Set oFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
   
    With oFileDialog
        If .Show Then
            sFolderPath = .SelectedItems(1) & "\"

           
            aFileNames = Split(FILE_NAMES, ";")
            aWshNames = Split(WSH_NAMES, ";")
            aRngAddress = Split(RNG_ADDRESS, ";")

           
            Set oMainWbk = ActiveWorkbook
           
            For i = 0 To UBound(aFileNames, 1)
                Set oCurrentWbk = Workbooks.Open( _
                    Filename:=sFolderPath & aFileNames(i))

                   
                With oCurrentWbk
                    Set oMainWsh = oMainWbk.Worksheets(aWshNames(i))
                    Set oCurrentWsh = .Worksheets(PRICE_WSH)

                   
                    For j = 0 To UBound(aRngAddress, 1)
                        oMainWsh.Range(aRngAddress(j)).Value = _
                            oCurrentWsh.Range(aRngAddress(j)).Value
                    Next j

                   
                    .Saved = True
                    .Close
                End With
            Next i
        End If
    End With

   
EXIT_SUB:
    Set oCurrentWsh = Nothing
    Set oMainWsh = Nothing

   
    If Not (oCurrentWbk Is Nothing) Then
        With oCurrentWbk
            .Saved = True
            .Close
        End With
    End If

   
    Set oCurrentWbk = Nothing
    Set oMainWbk = Nothing

   
    Erase aFileNames, aWshNames, aRngAddress
   
    Set oFileDialog = Nothing
   
    Application.ScreenUpdating = True
    Exit Sub

   
ERROR_HANDLER:
    ' Some code for error handling
    Err.Clear
    GoTo EXIT_SUB

End Sub

' ************************* ' ************************* '

 

Best regards.

macro to pick data from various files

hi,

First of all my sincere thanks to you for giving your precious time to reolve my problem.

However I tried to run the code but it din't work. May be, I m committing sm mistakes as I m new to VBA. Can you provide me your mail id so that I can send u all 3 sample files (2 working files along with the master file on which macro to be written).

Thanking you

RE: Macro...

Hi,

You can edit your forum topic and attach the sample files here.

Maybe the code not working because in your initial explanation you mentioned only two files and now you said "all 3 sample files". I made it to work based on yours initial requirements.

Please, explain one more time of what exactly you need.

 

Best regards.

Macro

Hi,

I could not get the option of attaching file so describing my exact requirement step by step as follows:-

a) There is a folder (named as working) in c drive which contains 17 excel workbooks. (17 products)

b) each workbook contains 4 sheets named as:- a) Cost, data, price, Rate.

c)Sheet named as Cost (in all 17 workbooks)is the one from where data to be picked by running macro.

d)There is a separate workbook named as MIS in other directory e.g. D drive.

e) MIS file will have 20 worksheets i.e. one worksheet for every product (17 in nos) plus 3 additional sheets which summarise the report.

f)We have nothing to do with 3 additional worksheets at this point of time.

g) All 17 worksheets will have same format. As apparant, 17 worksheets represent 17 products in such a way that worksheet 1 presents data of workbook number 1 (c:\working), worksheet 2 of workbook number 2 ((c:\working\*.xls))and so on.

h)Now following data in all 17 worksheets (in MIS workbook) to be picked from the 17 workbooks (C:\working\*.xls)

i) R15C2
ii) R18C2
iii) R23C2
iv) R30C2
v) R15C4
vi) R18C4
vii) R23C4
viii) R30C4
ix) R15C9
x) R18C9
xi) R23C9
xii) R30C9
xiii) R15C10
xiv) R18C10
xv) R23C10
xvi) R30C10

Earlier I gave reference of 2 sample files only coz I wanted the basic code to be written so that rest of the modifications/alterations could be done by myself.

I would be so grateful to you if you could help me out by providing the desired and much wanted solution of the above cited problem.

Thankin you.

RE: Macro to pick data from various files

Hi,

Okay, firstly, and this is my mistake, actually you never mentioned the total number of files. I don't know from where is my confusing. But this is not so important.

There is a little differences between your current and initial requirements, but this is not so important too, because of, as you said, you need a basic code on which you can make a modifications. And I wrote the above subroutine considering this possibility.

Into the part of code:

' Change the following with your real file name
Public Const FILE_NAMES As String = _
    "1.xlsx;2.xlsx;3.xlsx;4.xlsx;5.xlsx;6.xlsx;7.xlsx;8.xlsx;9.xlsx;10.xlsx"
' Change the following with your real worksheet name
Public Const WSH_NAMES As String = _
    "a;b;c;d;e;f;g;h;i;j"
' Change the following if necessary
Public Const PRICE_WSH As String = "Price"
' Change the following if you want another copy ranges
Public Const RNG_ADDRESS As String = _
    "C2;C10;C12;F2;F10;F12;J2;J10;J12"

make this changes:

' Change the following with your real file name
Public Const FILE_NAMES As String = _
    "ProductName1.xls;ProductName2.xls;ProductName3.xls;ProductName4.xls;ProductName5.xls;" & _
    "ProductName6.xls;ProductName7.xls;ProductName8.xls;ProductName9.xls;ProductName10.xls;" & _
    "ProductName11.xls;ProductName12.xls;ProductName13.xls;ProductName14.xls;ProductName15.xls;" & _
    "ProductName16.xls;ProductName17.xls"
' Change the following with your real worksheet name
Public Const WSH_NAMES As String = _
    "SheetName1;SheetName2;SheetName3;SheetName4;SheetName5;" & _
    "SheetName6;SheetName7;SheetName8;SheetName9;SheetName10;" & _
    "SheetName11;SheetName12;SheetName13;SheetName14;SheetName15;" & _
    "SheetName16;SheetName17"
' Change the following if necessary
Public Const PRICE_WSH As String = "Cost"
' Change the following if you want another copy ranges
Public Const RNG_ADDRESS As String = _
    "B15;B18;B23;B30;D15;D18;D23;D30;I15;I18;I23;I30;J15;J18;J23;J30"

Note: 1) ProductName1, and so on, must be replaced with your actual name of the product and the file extension.

 2) SheetName1, and so on, must be replaced with your actual name of the sheet that corresponding to ProductName1.

 3) Based on your new requirement, the name of the constant PRICE_WSH loses its meaning but this does not reflect on the code execution. If you want you can change its name, but don't forget to change it into the subroutine also.

Using of macro: 1) Paste the changed part of code together with subroutine into the standard code module in your MIS workbook.

 2) Run macro. You will be prompted to select the folder which contain product's files. Navigate to the folder and press OK button.

I hope now everything is clear enough.

 

Best regards.