Results 1 to 5 of 5

Thread: Compiling Workbook Data..

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

    Compiling Workbook Data..

    Many times Analysts need to compile data Form Different workbook into one workbook..and its a very time consuming task for them to Open all file one by one and manually copy and paste data into a single worksheet..So in that situation this Code provides a excellent way to do that work automatically and saves lot of time if All Workbook Contain the similar Data with One worksheet..Just Run this Code and get a Compiled File.



    Code:
    Sub Compile()
    On Error GoTo Err_Clear:
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim Fso As New Scripting.FileSystemObject
    Dim Path As String
    Dim Counter
    Dim File As File
    Dim FOlder As FOlder
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim AcWb As Workbook
    
    
    Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to Pick Files"
    Application.FileDialog(msoFileDialogFolderPicker).Show
    Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
    If Path = "" Then Exit Sub
    
    Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to Save Compiled File"
    CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show
    compiledPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
    If compiledPath = "" Then Exit Sub
       
    Set AcWb = ThisWorkbook
                 AcWb.Worksheets.Add.Name = "Index"
    
    Set FOlder = Fso.GetFolder(Path)
    
            For Each File In FOlder.Files
                 Counter = Counter + 1
                 Set wb = Workbooks.Open(Path & File.Name)
                   
                                If Application.Ready = True Then
                                     wb.Sheets("Index").UsedRange.Copy AcWb.Worksheets("index").Range("A" & Rows.Count).End(xlUp)
                                     Application.CutCopyMode = False
                                     wb.Close
                                 End If
    
            Next
    If Counter > 0 Then
    AcWb.SaveAs compiledPath & "Compiled"
    End If
    Err_Clear:
    Err.Clear
    Resume Next
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
            If Counter < 1 Then
            MsgBox "No File Found For Compile", vbInformation
            Else
            MsgBox Counter & " File Has been Compiled, Please Find your File at" & vbCrLf & compiledPath, vbInformation
            End If
    End Sub

  2. #2
    Junior Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0
    For this to work it is necessary:

    Load the Visual Basic Editor (ALT-F11)
    Select Tools - References from the drop-down menu
    A listbox of available references will be displayed
    Tick the check-box next to 'Microsoft Scripting Runtime'
    The full name and path of the scrrun.dll file will be displayed below the listbox
    Click on the OK button


    Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at \Windows\System32\scrrun.dll

    Sorry for my english

    Question:
    How to collect data from only the needed cells?
    How to change the code?
    Last edited by rusttem; 01-18-2013 at 11:04 PM.

  3. #3
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Change the Range to Copy in the below line

    wb.Sheets("Index").UsedRange.Copy AcWb.Worksheets("index").Range("A" & Rows.Count).End(xlUp)

    Regards
    Rajan.

  4. #4
    Junior Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0
    What can replace the UsedRange, if you want to select data from 2-3 different cell

  5. #5
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Union(cell1,cell2,cell3)

Similar Threads

  1. Replies: 2
    Last Post: 05-28-2013, 05:32 PM
  2. Replies: 0
    Last Post: 04-20-2013, 10:07 AM
  3. Moving Current Latest Data To New Workbook
    By Terry in forum Excel Help
    Replies: 1
    Last Post: 01-19-2013, 12:37 AM
  4. Update data from closed workbook with sumif formula
    By leopaulc in forum Excel Help
    Replies: 4
    Last Post: 11-02-2011, 02:58 AM
  5. Merging Data from many workbooks in to one workbook
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 14
    Last Post: 10-10-2011, 11:26 AM

Tags for this Thread

Posting Permissions

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