retrieve information from multiple files

mickhutton

New Member
Joined
Jan 26, 2005
Messages
5
I am wondering what the best way to retrieve data from multiple files in Excel is. To further explain, what I am trying to do is extract information on a daily basis from .xls files that are saved for each seperate day in a template form. I want to be able to run a spreadsheet that will read each files's data and record it in the form of a report spreadsheet.

Thanks for any help,

Mick
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
A few questions:
How many files are we talking?
Are they all in the same folder?
Is the macro going to determine the file names (I.e. do they have the date in them or are you doing the same files each time)?
Is the data in the same place for each of the files?
Is the data comeing from one sheet on each file or multiple sheets?
Where is the data being pasted in the "master" book (and does it overwrite data or append it to the bottom)?
 

mickhutton

New Member
Joined
Jan 26, 2005
Messages
5
Sorry to take so long to reply.....

As a background, I have files that report on daily production which I enter daily, from a template. All the files are stored in the same folder with a suffix "~050216.xls" indicatingthe date. In this case, the date indicated is 16th Feb 2005. I guess the macro will have to translate the current date in the file to search for the relevant file (does that make sense?) and the data is in the same place in each of the files, as they are using a template form.

The data will be coming from one sheet (for multiple dates) and will need to be reported on either a weekly (10 files- 5 days, 2 shifts) or monthly basis

In the "master" book, I want the info from the succeeding files to be appended under the preceeding file's information.

Thankyou for any help you can give.

Mick
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
Here's a macro that will merge all Excel files in a user-selected folder. I don't know your level of macro expertise so let me know if you need help implementing it:

Code:
Option Explicit
Public strPath As String

Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
                                     Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
                                   Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

    '   Root folder = Desktop
    bInfo.pidlRoot = 0&

    '   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If

    '   Type of directory to return
    bInfo.ulFlags = &H1

    '   Display the dialog
    x = SHBrowseForFolder(bInfo)

    '   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function


Sub MergeFiles()
    Dim strFolder As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet
    Dim wbSource As Workbook

    strFolder = GetDirectory("Select a folder containing Excel files you want to merge")
    If Len(strFolder) = 0 Then Exit Sub

    'Create a new workbook.  This will be for the merged data
    Set wbDest = Workbooks.Add
    Set shtDest = wbDest.Sheets(1)

    With Application.FileSearch
        .NewSearch
        .LookIn = strFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        If .FoundFiles.Count = 0 Then Exit Sub

        For lngFilecounter = 1 To .FoundFiles.Count

            Set wbSource = Workbooks.Open(.FoundFiles(lngFilecounter))
            ActiveSheet.Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).Copy
            shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1).PasteSpecial xlPasteAll
            wbSource.Close False
        Next lngFilecounter

    End With


End Sub
 

mickhutton

New Member
Joined
Jan 26, 2005
Messages
5
Wow! Thanks for that!

My level of macro experience is somewhat limited (I am an engineer with little talent at programming) but am learning.


I may need a little guidance regarding the implementation. Anythingthat you can give would be great.

Cheers,
Mick
 

mickhutton

New Member
Joined
Jan 26, 2005
Messages
5
okay, I have managed to run the macro... The only thing is that it is selecting the WHOLE spreadsheet data. How do I change the macro to only read certain cells and place them where I want?

I have made a worksheet in each file titled "report" which is 3 rows x 7 columns which is the info that I need to merge into one file.

Once again, thanks for the guidance.

Mick
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top