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:
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