Exclude Worksheets when extracting data

03856me

Active Member
Joined
Apr 4, 2008
Messages
297
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top