Check to see if sheet exists

[Steph]

Hi. Every week I run a timesheet procedure that opens all files within a
folder, and copies a specific section from a specific sheet within each
file. Can I first run a procedure that makes sure that the sheet called
"timesheet" exists in every file?

I can't just add an on error resume next block, becasue I need to identify
WHICH files do not have that particular sheet. Any help would be greatly
appreciated!

Cheers.

My code:
Sub OpenFiles()
'Opens Files in Folder

Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Timesheets to Include in SAP PO Upload",
MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook


' With ActiveWorkbook.Sheets("Timesheet").UsedRange
' .Value = .Value
' End With

wkbk.Sheets("Timesheet").Range("A10:AE" &
Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" &
Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial
Paste:=xlPasteValues
wkbk.Close
Next iFiles
End If

'Duplicate Test Here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Tom Ogilvy]

Sub OpenFiles()
'Opens Files in Folder

Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Timesheets to Include in SAP PO Upload",
MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook
On Error Resume Next
set sh = wkbk.worksheets("TimeSheet")
On Error Resume Next
if not sh is nothing then


' With ActiveWorkbook.Sheets("Timesheet").UsedRange
' .Value = .Value
' End With

wkbk.Sheets("Timesheet").Range("A10:AE" & _
Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" & _
Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial
_
Paste:=xlPasteValues
Else
msgbox wkbk.Name & " has no timesheet"
End if
wkbk.Close
Next iFiles
End If

'Duplicate Test Here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Steph]

Tom,

Thanks for the quick response! I tried, and it didn't error out when it
came across a file with no timesheet, but the message box at the end of your
code displaying which file didn't have it never displayed?

Thanks again!

 

[Tom Ogilvy]

If it didn't avoid that workbook, then you would have gotten an error on the
wkbk.Sheets("Timesheet").Range("A10:AE" & _
line, so it never would be to the msgbox line.

Did you get such an error.

Possibly above this line

wkbk.Sheets("Timesheet").Range("A10:AE" & _

put in
set sh = nothing

On Error Resume Next
set sh = wkbk.worksheets("TimeSheet")
On Error Resume Next
if not sh is nothing then

At the top do

dim sh as worksheet

This approach works.

 

[Steph]

I love you, Tom!! Thank you sooo much!!

If it didn't avoid that workbook, then you would have gotten an error on
the
wkbk.Sheets("Timesheet").Range("A10:AE" & _
line, so it never would be to the msgbox line.

Did you get such an error.

Possibly above this line

wkbk.Sheets("Timesheet").Range("A10:AE" & _

put in
set sh = nothing

At the top do

dim sh as worksheet

This approach works.