CODEWITHSUNDEEP
excelautomation
haakonlu
haakonlu

Reputation: 949

Importing data from a folder of excel-files to a specific excel-file for analysis

I'm working on a project where I am supposed to collect a specific table of data from weekly reports(excel documents), the data is sorted in lines, where each line contains a date, number of minutes, a code(1-7) and a comment.

I want each line from the repport to be imported into an existing excel-file("Mother-file"), which later on will be used for analysis-purposes.

Example: Let's say each weekly report has 2 lines of data(This will wary). After a month I would have 4 reports which should result in 8 lines in my "mother-file".

The challenge here is making this thing automated. I already know an easy way of inputing theese data into the "mother-file" ,but it is a manual task which I would like to get automated.

So far i've been using commands like the one under, and basically copying it several times, before i edit the name of the excel-file (140923.xlsx) (the date).

='F:\- RANHEIM\MPM\Bearbeidet resultatservice\[140923.xlsx]Sammendrag'!$B$4

So I'm thinking that maybe the best thing would be a command/code that imports every line in the table from the weekly report (excel-file), in a specific folder. And maybe even a command/code for removing unused lines in the "mother-file", which came from empty lines in a weekly report.

Data to be retrieved from reports, and saved to a "motherfile" The data to be retrieved is in fact three different types of data, that must be seen in corelation. Which brings me to my next question: - Will I have to make three "SourceRange" to do the collection of the three datatypes? Or can I collect and save an entire row illustrated in the picture, in the motherfile?

Upvotes: 0

Views: 1407

Answers (2)

TheEngineer
TheEngineer

Reputation: 1215

The closest I can get you to what you want to accomplish without using VBA is using the INDIRECT function:

Step 1: Have the user input the sheet name of the report into cell A1. I suggest using Data Validation to display an input message on the cell in case someone else uses the file:

Input Message

Step 2: In cell B1, put the following formula:

="'F:\- RANHEIM\MPM\Bearbeidet resultatservice\["&A1&"]Sammendrag'!A1"

It will return this:

Range Reference

Step 3: In cell A2, put the following formula:

=IF(OFFSET(INDIRECT($B$1),ROW(A2)-1,COLUMN(A2)-1)=0,"",OFFSET(INDIRECT($B$1),ROW(A2)-1,COLUMN(A2)-1))

Step 4: Drag the formula down and across so that all cells are pulled in from the report.

Step 5: The INDIRECT function requires that the referenced sheet is open to use. If you want the info from the report to stay in the mother file after the report is closed, just copy the used range, do a Paste Special over the same range, and select "Values". Add any headers you want and remove the Input Message, if necessary.

Step 6: If you want to remove the empty lines, you can then sort your range based on column A (the date column). This will put any blank lines at the bottom of the sheet; effectively removing them from your report.

Upvotes: 1

PermaNoob
PermaNoob

Reputation: 869

This is what I use. I've marked all the areas you would need to change to personalize it for your needs.

Sub MergeAllWorkbooks()
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, CalcMode As Long
Dim OldName As String, NewName As String

'***Change this to the path\folder location of your files.***
MyPath = "C:\Folder\Path"

' 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.
On Error GoTo ExitTheSub
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
    Exit Sub
End If
On Error GoTo 0

' 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


' 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

            '***Set this to the name or number of the worksheet the data will be put.***
            Set BaseWks = ThisWorkbook.Sheets("SheetName")
            '***Change this range to fit your own needs. This is where it will look for the data.***
            With mybook.Worksheets(1)
                Set sourceRange = .Range("A2:M10000")
            End With
            If Err.Number > 0 Then
                Err.Clear
                Set sourceRange = Nothing
            End If
            On Error GoTo 0
            rnum = BaseWks.Range("A" & Rows.Count).End(xlUp).Row + 1

            If Not sourceRange Is Nothing Then

                SourceRcount = sourceRange.Rows.Count

                    '***Set the destination range.***
                    Set destrange = BaseWks.Range("A" & rnum)

                    ' Copy the values from the source range
                    ' to the destination range.
                    With sourceRange
                        Set destrange = destrange. _
                                        Resize(.Rows.Count, .Columns.Count)
                    End With
                    destrange.Value = sourceRange.Value

            End If
            BaseWks.Columns.AutoFit
            Set sourceRange = Nothing
            Set destrange = Nothing
            Set BaseWks = Nothing



            With mybook
                '***Set the folder path for OldName to folder the file is located. Should be _
                the same as MyPath in this case.***
                OldName = "C:\Folder\Path\" & mybook.Name
                '***Set the folder path for NewName where you would like to move the files _
                once you've gotten the data from them. I generally just make another folder _
                inside of the first and call it "Used"***
                NewName = "C:\Folder\Path\Used\" & mybook.Name
                mybook.Close (False)
                Name OldName As NewName
            End With
        End If
    Next FNum
End If

ExitTheSub:
    On Error Resume Next
    ThisWorkbook.Save
    On Error GoTo 0

End Sub

Upvotes: 2

Related Questions