Import Data from Files in a Folder

[Stefan_Sand]

Hello,

i have the task to import a lot of data from multiple workbooks in a given folder (the amount of rows with data can vary, but the structure is always the same.

I came as far as i could set the folder for the data import. Now i need help to import the data.

Is there an easy way to do this? Please find the source data workbook as an example and the import files workbook as target??

many thanks in advance,
Stefan

Data to be imported from Import workbook to Import Files target workbook
 
In column A, 2nd row down sequential number xy for each row 
From B7 source sheet down to column D in target sheet , 2nd row down - value 
From C7 source sheet down to Column B in target sheet , 2nd row down
From cell C3 source sheet absolute Reference StartZeit in target Sheet column E, 2nd row down
From D7 source sheet to column F in target Sheet , 2nd row down - value
From Q7 source sheet to column G in target Sheet , 2nd row down - value
From R7 source sheet to column H in target Sheet , 2nd row down - value
From S7 source sheet to column I in target Sheet , 2nd row down - value
Target Sheet = Consolidated Data in this workbook

[HansV]

It's past midnight. I'll take a look later today.

[Stefan_Sand]

thank you, good night, sir.

[Rudi]

Hi Stefan,

Have you ever considered using Power Query, build right into Excel (in the DATA ribbon, Get and Transform group of buttons). Power Query (PQ) is the new replacement feature for the old Import External Data process, and with PQ it is really easy to import data (multiple CSV's or Workbooks) from a folder. Once the data is imported it is loaded to the target workbook as a table that is completely refreshable, meaning that if the source files change, it updates in the target file when you refresh the table.

Have a look at this link to illustrate the process: Power Query – Import All Files in a Folder

[Stefan_Sand]

Thank you Rudi, yes i did.
The real weird thing is, power query has been disabled for "security reasons......." - but the access to macros is enabled.

Stefan

;((

[HansV]

The following macro prompts for a folder using a different method, and imports the data.

Code: Select all

Sub ImportData()
    Dim strPath As String
    Dim strFile As String
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim m As Long
    Dim t As Long
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            strPath = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    Set wshT = ThisWorkbook.Worksheets("Consolidated Data")
    t = wshT.Range("A" & wshT.Rows.Count).End(xlUp).Row + 1
    strFile = Dir(strPath & "*.xls*")
    Do While strFile <> ""
        Set wbkS = Workbooks.Open(strPath & strFile)
        Set wshS = wbkS.Worksheets(1)
        m = wshS.Range("C" & wshS.Rows.Count).End(xlUp).Row - 6
        With wshT.Range("A" & t).Resize(m)
            .Formula = "=ROW()-1"
            .Value = .Value
        End With
        wshT.Range("B" & t).Resize(m).Value = wshS.Range("C7").Resize(m).Value
        wshT.Range("D" & t).Resize(m).Value = wshS.Range("B7").Resize(m).Value
        wshT.Range("E" & t).Resize(m).Value = wshS.Range("C3").Value
        wshT.Range("F" & t).Resize(m).Value = wshS.Range("D7").Resize(m).Value
        wshT.Range("G" & t).Resize(m).Value = wshS.Range("Q7").Resize(m).Value
        wshT.Range("H" & t).Resize(m).Value = wshS.Range("R7").Resize(m).Value
        wshT.Range("I" & t).Resize(m).Value = wshS.Range("S7").Resize(m).Value
        wbkS.Close SaveChanges:=False
        t = t + m
        strFile = Dir
    Loop
End Sub

[Rudi]

... power query has been disabled for "security reasons..."

Wow! That is a sin!
Why would they ever do that?

[Stefan_Sand]

i cannot write here, what was my reaction................
sorry, but it is true, may Dogbert come over me.... ;((((

[Stefan_Sand]

Hans, thank you very much.