Free Microsoft Excel 2013 Quick Reference

Collate data from multiple excel files Results

Hi, I'm new to this board. Here's my situation:- I have multiple excel files with a form popping up whenever these files are opened (form has been called in Workbook open function). I need to collate data from these multiple excel files without opening these files. Can somebody help with a sample code ??????

Hi everyone,

I would like to use Get external data in the Data tab to query data from
mulitple workbooks. By doing so, i supposed, there is no need to open the
workbooks and still get the data that i would like to have. The data from
these workbooks appears in the same format but it is not in a template/table
form.

However, I can only manage to query from a single file. How do I query and
collate all the data from mulitple excel files into a single worksheet?

Thanks in advance!

Hi,

Am amateur at best in VBA. I need a Macro which will collate value of cells (not formulas) from specific cells in each excel files in a folder, and then collate them in a separate excel file.

I have put together a code from bits and pieces from my earlier practice projects, however, as expected, it was not working. So after a lot of head scratching I modified the code to quite an extent and almost got it to work!

However, I am getting only the value of cell B5 from each file in the folder in all the 7 columns of BaseWks...

Am pretty much sure that my mistake is in the Range syntax or something like it...

Please Help!!!

Dim MyPath As String, FilesInPath As
String
Dim MyFiles() As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range
Dim rnum As Long
    
Sub MergeAllWorkbooks()

    ' Change this to the pathfolder location of your files.
    MyMPath = Worksheets(1).TextBox1.Text

    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "" Then
        MyPath = MyPath & ""
    End If
    
    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.xls")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files
    ' in the search folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    ' Set various application properties.
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    BaseWks.Range("a1").Resize(1, 7).Value = Array("SAP ID", "Name", "Designation",
"Supervisor", "Band", "Department", "Score")
    
    rnum = 1

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not mybook Is Nothing Then
                On Error Resume Next

                ' Change this range to fit your own needs.
                With mybook.Worksheets(1)
                    Set sourceRange = .Range("D5,D4,D6,D7,G5,G6,D33,G33")
                End With
                
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    ' Set the destination range.
                    Set destrange = BaseWks.Range("A" & rnum + 1)
                    
                    ' Copy the values from the source range
                    ' to the destination range.
                    With sourceRange
                        Set destrange = destrange.Resize(1, 7)
                    End With
                    destrange.Value = sourceRange.Value

                    rnum = rnum + 1
                End If
                mybook.Close savechanges:=False
            End If

        Next FNum
        BaseWks.Columns.AutoFit
    End If

    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Range("A1:G1").Select
    Selection.Font.Bold = True
    Columns("A:G").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub


Hi all,

I have the job of producing a survey in Excel and collating the results anonymously, to help with this I'm looking for a chunk of code that will enable me to pull a specified range of data from all the files in a particular drive with a filename starting with a specified word and insert them all in to the same blank sheet.

If anybody has any ideas/has done this before your help would be much appreciated.

Many thanks

Matt

Hi

I have found the following code that i believe will fetch data from multiple sheets and combine it in to one named "Rollup". This data is fetched from sheets named as per the standard excel format. However my sheets are being imported with the name of the file which they have come from for example:

FR1-22.11.2009.xls
RA12-22.11.2009.xls

The constant that will remain will be the date throughout the sheets names as the number of actual sheets present will vary. How would the code below be edited to reflect this naming convention? assuming the data is to be collated in to "rollup" and how would it be edited to pick out a specific cell or number of cells? at the minute it looks as though it just copies the whole sheet whereas i am looking to copy just specific cells in to "rollup".

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Rollup"


    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub
Thanks for any help

Hi,

I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.

I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.

Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.

Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!

**********************************************

For every day, a new worksheet is created by my colleagues which summarises the previous days production. My task is to create a worksheet which locates only a few specific cells within the daily summary worksheet and then prints the values into my master worksheet. This allows me to look at a specific couple of pieces of equipment and their specific production. (Production is not a number per se, but rather text. I don't see how this would affect the code, though, as it's simply coppying a cell).

This may appear easy to program, but the twist is the daily summary files are in folders corresponding to their month and year. So Febuary 1st will have its file in a directory different to January 31st and January 1st will be in a different year and month directory to december 31st. The directories differ by name only (C:yearmonth) for example.

I have attached my Master Document. It is called "MFI to well match". In my opinion, it does not contain any sensitive, confidential or innapropriate contents. You can ignore columns K onwards, they're just some simple code I wrote to rearrange some formula. It may serve to open it now so you can follow me. I have not attached a daily summary file since it contains confidential information.

Cells B4 to J4 are pieces of equipment, called MFI's. On any particular day, an MFI records the production of a specific well. This is the information I need to find from each daily summary file. You will notice I have manually entered them from the 1st of January to the 24th. That was time consuming and I hope to never repeat that process!!!

Ideally, would like my macro to do this:
- I select the row corresponding to a date, say row 25, which has already had its macro find and print the required data
- I drag that row down to copy everything to the next days row, row 26
- While both rows are highlighted (or maybe just row 26), pressing a button called "update" in the top will go and find my data for that specific day i.e. updating row 26 with its corresponding daily summary data.

My colleagues have a spreadsheet which does this already for something similar. (The reason I'm not getting them to help me now is because they're all busy of course!!)

This might help you with the solution:

The open master worksheet name: "MFI to well match.xls"
The closed worksheet name: "APF Daily Report 24.01.10.xls" obviously for the 24th of January 2010.
The directory for that file we can call: "C:Production2010January"
All of the cells in column B for "MFI to well match.xls" coresspond to cell $T$65 in the closed worksheets.
All of the cells in column C for "MFI to well match.xls" coresspond to cell $T$66 in the closed worksheets.
All of the cells in column D for "MFI to well match.xls" coresspond to cell $T$67 in the closed worksheets.
All of the cells in column E for "MFI to well match.xls" coresspond to cell $T$68 in the closed worksheets.
All of the cells in column F for "MFI to well match.xls" coresspond to cell $T$69 in the closed worksheets.
All of the cells in column G for "MFI to well match.xls" coresspond to cell $T$70 in the closed worksheets.
All of the cells in column H for "MFI to well match.xls" coresspond to cell $T$71 in the closed worksheets.
All of the cells in column I for "MFI to well match.xls" coresspond to cell $T$73 in the closed worksheets.
All of the cells in column J for "MFI to well match.xls" coresspond to cell $T$64 in the closed worksheets.

In advance, thank you so much for taking the time to help. It is very much appreciated. I am finishing work now, but I will call in tomorrow morning to check if any solutions have been found or if there are any further questions.

PS I hope the above wasn't too confusing.

Hi,

First timer here, so here we go!

I am trying to collate some data from a number of excel files into one worksheet.

The format of each file is the same. What I need to do basically is copy the last three columns of data from each file and paste them into the compiled results worksheet.

In other words,
Take the last 3 columns from File 1 and paste into Columns A-C
Take the last 3 columns from File 2 and paste into Columns D-F

and so on.......(there are hundreds of files!)

If anyone can help, that would be fantastic.

Many thanks.

Hi all,

Not sure if this can be done, I would like to collate excel email attachments from an outlook inbox. If possible I would like to save the files into a folder on a local drive. I would then like to log in a master document details within each attachment which will alwalys be held in the same cell location.

To add complication, not sure if this can be done, but would like to also save the attachment using all data from the FROM email address up until the '@' part then use a cell i.e. B5 inside the attachment itself as the rest of the filename.

Some emails will have multiple attachments so not sure if VBA can save each attachment in the way I described above.

Hope someone can help or at least get me started in the right place.

Thanks for any help
Pylorus

Hi,

I generally don't struggle when it comes to Excel and its functions, but someone asked me something the other day that I'm sure can be done but I can't think how.

I have multiple spreadsheets that have basically been created as forms that can be filled in, and I need to pull data from one field into a master spreadsheet to collate them all. The master file has a list of partners who are on our company rebate scheme with a unique identifier against each. The individual files to be collated are the exact same layout, and will have the unique identifier in somewhere, say cell A1 for argument's sake; then I need to match on this cell, to populate the sales target into the master file, but the target is in a different row and column, say D5.

Is there any way to either do a lookup from the master file to the individual files to bring back the contents of D5 where the master file has a match with A1? If this can't be done via a match, another option would be to generate a new file from all of the individual files, taking A1 and D5 from each and putting them on a new row in the same column of the master file, therefore generating a list of all of the details.

I'm sure it can be done somehow, but not sure if I'll need macros or to go into Access to do it? If I can't find a way of doing it, there are just over 100 forms that me and a colleague will have to copy and paste from to create the master file.

Hope I've explained the situation clearly enough without going on too much. Any help or advice will be much appreciated.


No luck finding an answer? You could always try Google.