Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Data from files (Excel 2003)

    Hello all,

    i have a problem with a large amount of excel files (about 300). every staff member has 4 files a year with 3 worksheets in it (1 sheet for a month), to track their time on projects (see example workbook). what i need to know is, how can i import some data from these workbooks (and sheets) -> i need to get a list with 4 columns, containing Name of the staff member ($C$1) - Month ($C$3) - ProjectSructure Element (PSP) number - always in row 6 and finally sum of hours- always in row 38. is there a possibility via vba to import these 4 different data from every sheet (if there is a PSP & Sum) into one? I tried with some code for importing text files but failed. any help would be appreciated....
    Stef
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data from files (Excel 2003)

    Store the workbooks in a dedicated folder that contains no other Excel workbooks. You can then run the following macro to import the data from the workbooks into the active workbook:
    <code>
    Sub Import()
    ' Modify as needed but keep trailing backslash
    Const strPath = "H:ExcelTimeSheets"
    Dim strFile As String
    Dim wbk As Workbook
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngTargetRow As Long
    Dim lngSourceCol As Long
    Dim lngLastCol As Long

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wshTarget = ThisWorkbook.Worksheets(1)
    lngTargetRow = wshTarget.Cells(Rows.Count, 1).End(xlUp).Row

    strFile = Dir(strPath & "*.xls")
    ' Loop through workbooks in folder
    Do While Not strFile = ""
    ' Open workbook
    Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
    ' Loop through the worksheets
    For Each wshSource In wbk.Worksheets
    ' Last used column
    lngLastCol = wshSource.Cells(6, Columns.Count).End(xlToLeft).Column
    ' Loop through columns
    For lngSourceCol = 4 To lngLastCol
    ' New row in target sheet
    lngTargetRow = lngTargetRow + 1
    ' Name to column A
    wshTarget.Cells(lngTargetRow, 1) = wshSource.Cells(1, 3)
    ' Month to column B
    wshTarget.Cells(lngTargetRow, 2) = wshSource.Cells(3, 3)
    ' PSP to column C
    wshTarget.Cells(lngTargetRow, 3) = wshSource.Cells(6, lngSourceCol)
    ' Hours to column D
    wshTarget.Cells(lngTargetRow, 4) = wshSource.Cells(38, lngSourceCol)
    Next lngSourceCol
    Next wshSource
    ' Close workbook
    wbk.Close SaveChanges:=False
    ' On to the next one
    strFile = Dir
    Loop

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>

    You'll have to modify the value of the constant strPath to match the folder you're using.
    See the attached sample workbook. It contains a button from the Forms toolbar that will run the macro.
    Warning: don't store the workbook with the macro in the same folder as the timesheet workbooks.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data from files (Excel 2003)

    Hi Hans,
    thanks a lot for your support, saving my day....
    the code fits perfect.....
    Stef

  4. #4
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data from files (Excel 2003)

    i have only a last question to this: if i want to take the data from only a special worksheet, i should change wbk.worksheets to wbk.worksheet("XYZ") and if
    the data are in columns the reference from lngSourceCol & lngLastCol to lngLastRow and lngLastRow??

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data from files (Excel 2003)

    If you want to retrieve data from sheet XYZ only, change

    For Each wshSource In wbk.Worksheets

    to

    Set wshSource = wbk.Worksheets("XYZ")

    and remove the line

    Next wshSource

    If the data are oriented differently, you'll have to switch the role of rows and columns throughout.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •