Test if Sheet Exists - Tom Ogilvy

[Steph]

Hi Tom,

Remember last night you modified some code for me to include a message box
for files that did not contain the sheet "timesheet" (code below). Is it
possible to write the files that do not have that sheet to a log (maybe a
text file, or even the immediate window) as well as the message box? I was
hoping to add some code that would kick off this procedure automatically,
and didn't want the message box to hold up the procedure waiting for the ok
click.

Thanks in advance!


Sub OpenFiles_New()
'Opens Files in Folder

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

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

Set sh = Nothing
On Error Resume Next
Set sh = wkbk.Worksheets("TSData")
On Error Resume Next
If Not sh Is Nothing Then

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

wkbk.Sheets("TSData").Range("A10:AG" & _
Sheets("Timesheet").Range("A20").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 & " does not have the TSData sheet"
End If
wkbk.Close
Next iFiles
End If

'**********************
'Duplicate Test Here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Bob Kilmer]

'to Immediate Window
Debug.Print wkbk.Name & " does not have the TSData sheet"

 

[Bob Kilmer]

'maybe a text file

Sub OpenFiles_New()
...
Call LogToFile(wkbk.Name & " does not have the TSData sheet")
...
End Sub

Private Sub LogToFile(msg As String)
Dim f As Integer
Dim path As String
path = "C:\"
f = FreeFile
Open path & "\MyOutput.txt" For Append As #f
Print #f, msg
Close #f
End Sub

This is convenient, but it opens and close the file for every write. If you
need more speed, store the names in ram, open the file, write them out,
close the file. This will only be an issue for huge numbers of writes
(10's - 100's of thousands or more), IMHO

Bob.

 

[Bob Kilmer]

Yeah, I know my name is not Tom, but I think he will understand. B^)

 

[Steph]

Hi Bob,

Thanks for the responses!! Didn't mean to single out only Tom....I just
figured it would be easier since he edited the code last night. But I
should have known....all you guys on this board are damn brilliant!

To your first response, the immediate window is nice, but a record in a file
would be nicer.

As for the text file and speed, this code will copy 300-400 files....so that
means the text file would be opened 300 -4oo times? Or only for each
instance where TSdata does not exist? If the latter is the case, that
should only occur 10-20 times at most, and therefore would be a great
option.

BUT, being as ignorant as I am regarding this stuff, how would I add your
text file code into the code I originally posted?

Thanks again Bob!

-Steph

 

[Bob Kilmer]

1. Copy the Sub LogToFile into the workbook.

2. Add this

Call LogToFile(wkbk.Name & " does not have the TSData sheet")

or this

LogToFile wkbk.Name & " does not have the TSData sheet"

before or after or inplace of the Msgbox line.

The Sub opens and close the file each time it is called, which would be each
time TSdata doesn't exist (the else case of your loop). I don't think the
lag due to the multiple opens/closes would be noticeable to you, but, below,
I have integrated the file open, print and close into the code, as I might
do if it were mine.

Option Explicit

Sub OpenFiles_New()
'Opens Files in Folder

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

'var's for saving to a text file
'''''''''''''''''''''''''
Dim f As Integer
Dim fname As String
Dim path As String
' change path or name to suit
path = "C:\"
fname = "MyOutput.txt"
f = FreeFile
'''''''''''''''''''''''''

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

'''''''''''''''''''''''''
'Open the file before the loop
'
''For Append' means we will perpetually add to the file.
'If you want to overwrite each time, use 'For Output'.
'If you want a different file each time, use 'For Output'
'and assign a new, unique name each time the macros is
'run - with a date/time string like this, perhaps
'
'fname = "XYZ_" & Format(Now(),"yyyymmddhhmmss") & .txt
'
Open path & fname For Append As #f
'''''''''''''''''''''''''
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook

Set sh = Nothing
On Error Resume Next
Set sh = wkbk.Worksheets("TSData")
On Error Resume Next
If Not sh Is Nothing Then

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

wkbk.Sheets("TSData").Range("A10:AG" & _
Sheets("Timesheet").Range("A20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" & _
Consol.Range("E65536").End(xlUp).Offset(1, 0).Row). _
PasteSpecial Paste:=xlPasteValues
Else

'''''''''''''''''''''''''
'Print to text file
Print #f, wkbk.Name & " does not have the TSData sheet"
'''''''''''''''''''''''''
'You could keep the debug.print here, also if you like.
Debup.Print wkbk.Name & " does not have the TSData sheet"

'MsgBox wkbk.Name & " does not have the TSData sheet"

End If
wkbk.Close
Next iFiles


'''''''''''''''''''''''''
'close the file after the loop
'and before exiting the sub
Close #f

'''''''''''''''''''''''''
End If

End Sub

 

[Steph]

Beautiful!! Thank you SO much Tom, uh, I mean Bob B^)

I really appreicate you time and help! Thanks again.

-Steph

 

[Bob Kilmer]

Thanks very much for the "flowers." Not only are we brilliant, but most of
us practice a bit, I would bet. <g>

BTW, please do not use End as you have. It is an abortive end and best to
avoid except when an abortive end is better that the alternatives (rare).

Prefer

To

Bob

 

[Steph]

Good advice. Thank you!
As far as practice goes - I'm sure you get your fair share! I for one have
learned an immense amount from this newsgroup. Some days, half my day is
spent reading posts of interest, and printing them put as well! And approx
1 year later, I know a LOT more, and probably 0.0001% of what you guys know!
Some day...... :^). Thanks again!