Exclude Worksheets when extracting data

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Exclude Worksheets when extracting data

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    246
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exclude Worksheets when extracting data

     
    I have Workbook1 that has macros that open all the files in a specific folder and loops through all the worksheets to pull specific data back into Workbook1. This has worked great for many years. As part of my new project I have added 4 new worksheets to each of the files in the folder. I need to ignore those four folders when extracting data. I have played around with this for a few days to no avail. Can someone advice me on how to ignore Sheet1, Sheet2, Sheet3 and Sheet4 when extracting data.

    Here is my working code:
    Code:
    Public Sub ExtractData16(ByRef tRow As Integer, ByRef sDataPath As String, ByRef objMainData As Worksheet, ByRef sDataFile As String, objMainSheet As Worksheet)
    '
    ' ExtractData for the payroll on the 16th of the month Macro
    
        Application.ScreenUpdating = False
        DoEvents
      
      Dim iRow As Integer 'Value for the Row that it is grabing from the initial Payroll Timesheet File
      'Dim tRow As Integer 'Value for the Row on which row the Macro will place the data Target
      Dim iHour As Integer 'Value for the Row for the Hour from the Input Payroll Timesheet file
      Dim tHour As Integer 'Value for the Row for the Hour going to the Target Data File.
      Dim iSheet As Integer 'This represents the number of sheets in a file.
      Dim nSheets As Integer 'This number represents the total sheet in the spreadsheet.
      
      Dim objApp As New Excel.Application
      Dim objBook As New Excel.Workbook
      Dim objSheet As Excel.Worksheet
        
      Set objBook = Workbooks.Open(sDataPath & sDataFile) '.Add(Template:=sDataPath & sDataFile)  'Your excel spreadsheet file goes here
      Set objApp = objBook.Parent
      
      nSheets = objBook.Sheets.Count 'ActiveWorkbook.Sheets.Count
      objBook.Windows(1).Visible = False
      objApp.Visible = True
      For iSheet = 2 To nSheets 'For next loop for each sheet in Worksheet.
        Set objSheet = objBook.Worksheets(iSheet) 'Name of sheet you want to View
        objSheet.Visible = xlSheetHidden
        
        objMainSheet.Cells(10, 3) = nSheets - iSheet
        objMainSheet.Cells(10, 4) = objSheet.Name
        tRow = tRow + 1     'Increments the next row down in the target data file
        'Get Absentee Codes
        objMainData.Cells(tRow, 1).Value = objSheet.Cells(1, 1) 'Company Name
        objMainData.Cells(tRow, 2).Value = objSheet.Cells(31, 5) 'Supervisor
        objMainData.Cells(tRow, 3).Value = objSheet.Cells(2, 1) 'Employee #
        objMainData.Cells(tRow, 4).Value = objSheet.Cells(3, 1) 'Employee Name
        objMainData.Cells(tRow, 12).Value = objSheet.Cells(7, 8) 'SDay
        objMainData.Cells(tRow, 13).Value = objSheet.Cells(9, 13) 'SWday
        objMainData.Cells(tRow, 5).Value = objSheet.Cells(13, 1) 'Mill
        objMainData.Cells(tRow, 6).Value = objSheet.Cells(13, 2) 'Dept
        objMainData.Cells(tRow, 7).Value = objSheet.Cells(13, 3) 'CIP
        objMainData.Cells(tRow, 8).Value = objSheet.Cells(13, 4) 'Shift
        objMainData.Cells(tRow, 9).Value = "A" 'Tell that it is an Absentee Code
        
        'Input Day 16 - end of month
        tHour = 30
        iHour = 13
        For iHour = 13 To 28
          objMainData.Cells(tRow, tHour).Value = objSheet.Cells(12, iHour)
          tHour = tHour + 1
        Next iHour
      Next iSheet
      
      objBook.Close False
      Set objBook = Nothing
      Set objMainSheet = Nothing
      Set objApp = Nothing
      
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,237
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Worksheets when extracting data

    here a Validate folder function inspects the path and determines if the folder is legal....

    Code:
    '... your code
    if IsValidPath(sDataPath) then
       Set objBook = Workbooks.Open(sDataPath & sDataFile) '.Add(Template:=sDataPath & sDataFile)  'Your excel spreadsheet file goes here
        '...code
    endif
    
    '...
    end sub
    
    '-------------
    public function IsValidPath(pvPath) as boolean
    '-------------
    dim colBad as new collection
    dim vWord
    
    IsValidPath=true
    
       'put bad folders here:
    colBad.add "\BadFolder1"
    colBad.add "\BadFolder2"
    colBad.add "\BadFolder3"
    
    for each vWord in colBad
       if instr(pvPath, vWord) >0 then 
           IsValidPath =false 
           goto endIt
       endif 
    next
    endIt:
    set colBad = nothing
    end function
    Last edited by ranman256; Nov 13th, 2017 at 02:06 PM.

  3. #3
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    246
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Worksheets when extracting data

    I am not having any issues with the file path or the folder - however, I need the code to ignore 4 worksheets but extract data from the other worksheets in the file.

  4. #4
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    246
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Worksheets when extracting data

      
    bump....I really need help with this code. I have not been working on this project for a few weeks but I am back on it.

    HOW do I EXCLUDE certain worksheets from the workbooks when I open them.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com