Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Check to see if sheet exists
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 312
Default Check to see if sheet exists
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Check to see if sheet exists


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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 312
Default Check to see if sheet exists
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" wrote in message
...


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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Check to see if sheet exists
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.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...


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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 312
Default Check to see if sheet exists
I love you, Tom!! Thank you sooo much!!

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...


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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to check to see if a sheet with a particular name exists? Varun Excel Worksheet Functions 3 January 25th 09 01:41 PM
check if sheet exists mohavv Excel Discussion (Misc queries) 1 November 21st 07 01:58 AM
check if the sheet/tag exists Alex Excel Worksheet Functions 2 March 14th 06 08:58 PM
Check whether data exists in Sheet.. Soniya Excel Programming 1 August 28th 03 08:26 AM
check if sheet exists Ross[_6_] Excel Programming 3 July 25th 03 06:46 PM


All times are GMT +1. The time now is 06:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement