Elgskred

ReadFilesFromFolder

Mar 7th, 2014
218
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub CopyFromWorkbooks()
  2.  
  3.   Dim DstRng As Range
  4.   Dim DstWks As Worksheet
  5.   Dim R As Long
  6.   Dim SrcRng As Range
  7.   Dim SrcWks As Worksheet
  8.   Dim SrcWkb As Workbook
  9.   Dim FileName As String
  10.   Dim FilePath As String
  11.   Dim Folder As Variant
  12.   Dim Title As String
  13.  
  14.     Sheets("Quality Hours").Select
  15.     Set DstWks = ActiveSheet
  16.     Set DstRng = DstWks.Range("A2")
  17.    
  18.     Set RngEnd = DstWks.Cells(Rows.Count, DstRng.Column).End(xlUp)
  19.     Set DstRng = IIf(RngEnd = "", DstRng, RngEnd.Offset(1, 0))
  20.  
  21.    
  22.       FilePath = "xx"
  23.       FileName = Dir("xx" & "\*.xlsm")
  24.      
  25.       If FileName = "" Then
  26.          MsgBox "No Excel workbooks were found in this directory.", vbCritical
  27.          Exit Sub
  28.       End If
  29.      
  30.       Application.ScreenUpdating = False
  31.      
  32.         Do While FileName <> ""
  33.           On Error GoTo ErrorExit
  34.           Set SrcWkb = Workbooks.Open(FileName:=FilePath & "\" & FileName, ReadOnly:=True)
  35.             For Each SrcWks In SrcWkb.Worksheets
  36.             Select Case SrcWks.Name
  37.             Case "Sheet1"
  38.               DstRng.Offset(R, 0) = SrcWks.Range("D4")
  39.               DstRng.Offset(R, 1) = SrcWks.Range("F4")
  40.               DstRng.Offset(R, 2) = SrcWks.Range("I8")
  41.               DstRng.Offset(R, 3) = SrcWks.Range("G6")
  42.               DstRng.Offset(R, 4) = SrcWks.Range("A6")
  43.               DstRng.Offset(R, 5) = SrcWks.Range("G4")
  44.             Case "Sheet2"
  45.               DstRng.Offset(R, 6) = SrcWks.Range("A2")
  46.               DstRng.Offset(R, 7) = SrcWks.Range("B2")
  47.               DstRng.Offset(R, 8) = SrcWks.Range("C2")
  48.               DstRng.Offset(R, 9) = SrcWks.Range("D2")
  49.               DstRng.Offset(R, 10) = SrcWks.Range("E2")
  50.               DstRng.Offset(R, 11) = SrcWks.Range("F3")
  51.             End Select
  52.              
  53.             Next SrcWks
  54.           SrcWkb.Close False
  55.           FileName = Dir()
  56.           R = R + 1
  57.         Loop
  58.        
  59. ErrorExit:
  60.       Application.ScreenUpdating = True
  61.       If Err <> 0 Then
  62.          MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & Err.Description
  63.       End If
  64.  
  65. End Sub
Advertisement
Add Comment
Please, Sign In to add comment