retrieve information from multiple files

[mickhutton]

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

 

[vconfused]

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]

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]

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:

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]

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]

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