+ Reply to Thread
Results 1 to 8 of 8

VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HELP!)

  1. #1
    Registered User
    Join Date
    01-06-2013
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HELP!)

    Hi all,

    I am an absolute beginner in VBA, I desperately need a solution for my problem at work.

    I have multiple workbooks that generate a sheet named "extraction Data" which is the 11th sheet in the workbook, with cell references A1-X5. Now these workbooks are completed on a daily basis and are saved with the filename format "ROV dd mmm yyyy" e.g. ROV 07 Oct 2013. These files are then stored in a week commencing folder, e.g. WC 07 Sep 2013. These folders are then stored in a month folder, e.g. October 2013. These folders are then stored in a Title Folder, e.g. ROV. This is the complete structure. I then have multiple title folders with the names of L03, L05, L06, L08, Rov, L7a - all containing the same structures, down the the filename headers.

    What I would like to do, is make a 'master sheet' which copies the 'extraction data' sheets from these workbooks and pastes them in a list on a single sheet. I am unsure of the best, easiest, and most thorough way of doing this and would like input and help of anybody willing. Your thanks in advance.

    I am more than happy to send examples and files over to an email, but cannot really post here because of confidentiality reasons.

    Please Help me!

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hi there,

    Your task sounds as if it's a perfect example of what Office Automation was designed for!

    I'd need some clarification regarding your "Master Sheet" - is it really a single worksheet which contains the range A1:X5 from each of the (very!) many workbooks associated with every Title?

    If you want to let me have a look at one or two typical workbooks just let me know & I'll send you a private post with an email address you can use.

    Hope this sounds helpful!

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    01-06-2013
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hi Greg, thanks for your interest.

    Yes you are correct, there are many many workbooks, which all generate data into a sheet
    Within them called "Extraction Data" these workbooks are saved under a file name with the
    Format as follows: production line code, dd mmm yyyy for example "Rov 08 Oct 2013"
    These files are then saved in a week commencing folder, e.g. "WC 07 Oct 2013" these are
    Then saved in a month folder e.g. "October 2013".
    These are then saved in a production line code folder, e.g. "Rov".
    I have had a reply of a guy who has wrote a vba code which operates a macro in a new sheet called "Master" which pulls the data and lists it. The only problem is, some of the production lines do not run every single day, so some days have sheets missing, and the macro pulls up a runtime error because of this.

    Thanks for your help!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hi again,

    Thanks for that information.

    Could you post a copy of the workbook (without the data if you like) which contains the macro you've been using to populate your Master Sheet with the extraction data from the various workbooks. That should give me a better idea of how to proceed and how to deal with the error generated when a data workbook is missing.

    Regards,

    Greg M
    Last edited by Fotis1991; 10-09-2013 at 05:59 AM. Reason: Upload a sample in our forum is ok.Pls see rule#4. Thank you!

  5. #5
    Registered User
    Join Date
    01-06-2013
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    It would be better to email you the test folder structure with workbooks in them, along with the macro-enabled test "Master" data input sheet. The reason for this is that the workbooks may contain confidential information about the company I work for and the products we produce. Thanks

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hi again,

    I've sent you a private message with my email address - send me your workbook(s) and I'll have a look at them.

    Regards,

    Greg M

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hi again,

    Glad to know that the workbook seems to be doing what you need!

    As discussed, I'm posting the Master workbook (without data) in case the solution may be of use to other forum users.

    Best regards,

    Greg M
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-06-2013
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to extract sheet in multiple closed workbooks to a sing sheet on new workbook (HE

    Hello all,

    I seem to have run into quite a frustrating issue with my macro. The problem I seem to be having is it disregards some sheets to import and it seems the issue lies within the Week Commencing part of the file structure.
    For instance, the macro disregarded a sheet with the filename 'Rov 02 Jan 2014', this sheet was at the end of the path 'S:\Common\WLR Downtime Sheets\ROV\January 2014\WC 30 December 2013\.
    I tried moving the sheet into another Week Commencing folder named '01 Jan 2014' but the macro presumably noted that the 1st Jan was not a Monday.

    What I would like to do is edit my macro to take away and formatting of folders and filenames, and just look for any workbook within S:\Common\WLR Downtime Sheets\, open each one and look at the date in cell 'D2' of the 'Hourly Record' tab, and if this date is equal to or greater than the date I input in the MsgBox, copy and paste, and if the date is less than, disregard. I would love also for the macro to generate a log into another tab, which shows me exactly which sheets it has looked at, and which sheets it has disregarded or interrogated.

    Any help would be hugely appreciated,

    Many Thanks




    Option Explicit


    '=========================================================================================
    '=========================================================================================


    ' This is the Folder which contains all of the 'Title' subfolders
    ' NOTE: This value must include a backslash (\) chatacter at the end!

    Const msROOT_FOLDER As String = "S:\Common\WLR Downtime Sheets\"

    ' This is the value used when testing on Lew's computer
    'Const msROOT_FOLDER As String = "J:\Worksheets\Excel Forum - 2013-09-19\" & _
    "Extract Multiple Worksheets\WLR Downtime Sheets\"

    Const msWEEK_COMMENCING As String = "WC "
    Const msFILE_SUFFIX As String = ".xlsx"

    Const msSOURCE_SHEET As String = "Extraction Data"
    Const msSOURCE_RANGE As String = "A2:DX4"

    Const mlRED As Long = 8421631


    '=========================================================================================
    '=========================================================================================


    Public Sub ImportExtractionData()

    Application.ScreenUpdating = False
    Application.Calculation = Excel.xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    Const sFORMAT_MONTH As String = "mmmm yyyy"
    Const sFORMAT_DAY As String = "dd mmm yyyy"
    Const sHEADER As String = "tblHeader"
    Const sFSO As String = "Scripting.FileSystemObject"

    Dim objTitleFolder As Object
    Dim objFSO As Object

    Dim dteWeekCommencing As Date
    Dim dteStartDate As Date
    Dim dteFileDate As Date

    Dim sSourceFileName As String
    Dim sPasteAddress As String
    Dim sStartDate As String

    Dim rDestinationCell As Range
    Dim rRangeToPaste As Range

    Dim wbkSource As Workbook
    Dim wksSource As Worksheet

    Dim iNoOfSourceColumns As Integer
    Dim iNoOfSourceRows As Integer

    On Error GoTo ErrorEncountered

    ' Exit from this routine if the Root Folder cannot be located
    If Dir(msROOT_FOLDER, vbDirectory) = vbNullString Then
    MsgBox "The folder """ & msROOT_FOLDER & """ cannot be located", vbCritical
    GoTo ExitPoint
    End If

    ' Get the number of rows and columns in the source range
    iNoOfSourceColumns = wksMaster.Range(msSOURCE_RANGE).Columns.Count
    iNoOfSourceRows = wksMaster.Range(msSOURCE_RANGE).Rows.Count

    ' Define a range which has the same dimensions as the Source range - i.e. the
    ' size of the range to be pasted
    With wksMaster
    Set rRangeToPaste = Range(.Cells(1, 1), _
    .Cells(iNoOfSourceRows, iNoOfSourceColumns))
    End With

    ' Get the address of the above range - this will later be used as an offset from
    ' the Destination Cell for the Paste operation
    sPasteAddress = rRangeToPaste.Address

    ' Get the date from which the file import should start - subfolders in the "WLR
    ' Downtime Sheets" folder will be searched for filenames which contain a date
    ' between the specified date and today, and data on the "Extraction Data"
    ' worksheet in those files will be copied to the "Master" worksheet
    sStartDate = InputBox("Please Enter The Start Date Of The Data You Wish To Import", "Let's Pull Some Data!!", _
    Format(Date - 1, sFORMAT_DAY))

    On Error Resume Next
    dteStartDate = CDate(sStartDate)
    If dteStartDate = 0 Then GoTo ExitPoint
    On Error GoTo ErrorEncountered

    ' Determine the destination cell on the Master sheet where the data will first be
    ' imported to - this will be the cell below the Header Row in Column A
    Set rDestinationCell = wksMaster.Range(sHEADER).Cells(1, 1).Offset(1, 0)

    Set objFSO = CreateObject(sFSO)

    ' For each date from the Start Date to today, i.e. in date order
    For dteFileDate = dteStartDate To Date

    ' dteWeekCommencing is the date of the Monday of the week in question
    dteWeekCommencing = dteFileDate - Weekday(dteFileDate, vbMonday) + 1

    ' For each 'Title' subfolder in the root folder . . .
    For Each objTitleFolder In objFSO.GetFolder(msROOT_FOLDER).SubFolders

    ' Use the current 'Title' subfolder, the Week Commencing date and
    ' the FileDate to the full filename of the source workbook
    sSourceFileName = objTitleFolder.Path & "\" & _
    Format(dteWeekCommencing, sFORMAT_MONTH) & "\" & _
    msWEEK_COMMENCING & _
    Format(dteWeekCommencing, sFORMAT_DAY) & "\" & _
    objTitleFolder.Name & " " & _
    Format(dteFileDate, sFORMAT_DAY) & msFILE_SUFFIX

    ' Check that the source workbook exists
    If Dir(sSourceFileName) <> vbNullString Then

    Debug.Print sSourceFileName

    ' "Flash-update" the display so that the user can see some progress
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False

    ' Open the source workbook and locate the Source worksheet it contains
    On Error Resume Next
    Set wbkSource = Workbooks.Open(sSourceFileName)
    Set wksSource = wbkSource.Sheets(msSOURCE_SHEET)
    On Error GoTo ErrorEncountered

    If Not wksSource Is Nothing Then

    wksSource.Range(msSOURCE_RANGE).Copy
    rDestinationCell.Range(sPasteAddress).PasteSpecial _
    Paste:=xlPasteFormats
    rDestinationCell.Range(sPasteAddress).PasteSpecial _
    Paste:=xlPasteValues

    Else: rDestinationCell.Range(sPasteAddress).Interior.Color = mlRED

    End If

    ' Disable DisplayAlerts to avoid the "Lot of Data in Clipboard" prompt
    Application.DisplayAlerts = False
    wbkSource.Close SaveChanges:=False


    ' Move the destination cell downwards by the appropriate number of rows
    Set rDestinationCell = rDestinationCell.Offset(iNoOfSourceRows, 0)

    ' Park the cursor to unselect the range which has just been pasted
    rDestinationCell.Select

    End If

    Next

    Next

    ' Park the cursor now that the data importation has been completed
    wksMaster.Range(sHEADER).Cells(1, 1).Offset(1, 0).Select
    Application.ScreenUpdating = True
    Application.Calculation = Excel.xlManual
    Application.EnableEvents = True
    Application.DisplayAlerts = True

    MsgBox "The Data Import Process Has Completed, Press OK To Acknowledge"

    ExitPoint:

    Exit Sub

    ErrorEncountered:

    Resume ExitPoint

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet
    By Jennasis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2013, 06:11 AM
  2. Import data from several closed workbooks to a single sheet in an open workbook
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2013, 06:35 AM
  3. Extract data from closed workbooks and copy into new workbook
    By philaugust2004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2013, 07:45 AM
  4. Copy Sheet from one Workbook to Multiple Closed Workbooks
    By sflexi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2013, 10:01 AM
  5. Extract Data from Multiple Workbooks to One Sheet
    By jketcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2012, 04:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1