Re: Test if Sheet Exists - Tom Ogilvy

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob Kilmer (rprgrmr_at_yahoo.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 22:07:10 -0400

1. Copy the Sub LogToFile into the workbook.

2. Add this

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

or this

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

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 Work***

  '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 ***"
        '''''''''''''''''''''''''
        'You could keep the debug.print here, also if you like.
        Debup.Print wkbk.Name & " does not have the TSData ***"

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

      End If
      wkbk.Close
    Next iFiles

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

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

End Sub

"Steph" <verysmallrox@yahoo.com> wrote in message
news:%23uijpZQoEHA.2948@TK2MSFTNGP11.phx.gbl...
> 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" <rprgrmr@yahoo.com> wrote in message
> news:eT208MQoEHA.2304@TK2MSFTNGP14.phx.gbl...
> > Yeah, I know my name is not Tom, but I think he will understand. B^)
> >
> > "Bob Kilmer" <rprgrmr@yahoo.com> wrote in message
> > news:%23BdZzKQoEHA.3324@TK2MSFTNGP10.phx.gbl...
> >> 'maybe a text file
> >>
> >> Sub OpenFiles_New()
> >> ...
> >> Call LogToFile(wkbk.Name & " does not have the TSData ***")
> >> ...
> >> 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.
> >>
> >> "Steph" <verysmallrox@yahoo.com> wrote in message
> >> news:%23BGzLtPoEHA.2684@TK2MSFTNGP11.phx.gbl...
> >> > Hi Tom,
> >> >
> >> > Remember last night you modified some code for me to include a
message
> > box
> >> > for files that did not contain the *** "time***" (code below).
Is
> > it
> >> > possible to write the files that do not have that *** 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 Work***
> >> >
> >> > 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 ***"
> >> > End If
> >> > wkbk.Close
> >> > Next iFiles
> >> > End If
> >> >
> >> > '**********************
> >> > 'Duplicate Test Here
> >> >
> >> > Application.ScreenUpdating = True
> >> > Application.DisplayAlerts = True
> >> >
> >> > End Sub
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Quantcast