Re: Test if Sheet Exists - Tom Ogilvy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steph (verysmallrox_at_yahoo.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 23:37:01 -0400

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!

"Bob Kilmer" <rprgrmr@yahoo.com> wrote in message
news:O6k3RsRoEHA.1176@TK2MSFTNGP12.phx.gbl...
> 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
>> > If TypeName(GetFiles) = "Boolean" Then
>> > MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
>> > Exit Sub
>> > Else
>
> To
>> > If TypeName(GetFiles) = "Boolean" Then
>> > MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
>> > End
>> > Else
>
> Bob
>
> "Steph" <verysmallrox@yahoo.com> wrote in message
> news:OpSC5LRoEHA.1460@TK2MSFTNGP12.phx.gbl...
>> Beautiful!! Thank you SO much Tom, uh, I mean Bob B^)
>>
>> I really appreicate you time and help! Thanks again.
>>
>> -Steph
>>
>> "Bob Kilmer" <rprgrmr@yahoo.com> wrote in message
>> news:uL9hFIRoEHA.3172@TK2MSFTNGP10.phx.gbl...
>> > 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" <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 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.
>> >> >>
>> >> >> "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 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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Bob Hoover Short Story
    ... Dudley Henriques wrote: ... my advice is to practice what you'd say to him if you did meet ... of nowhere a colleague says "Hey, Bob Hoover's over there and he's ... if I might respectfully offer a bit of advice from ...
    (rec.aviation.military)
  • Re: Bob Hoover Short Story
    ... like him, my advice is to practice what you'd say to him if you did meet him so you don't feel like a sophomoric tongue-swallowing idiot if out of nowhere a colleague says "Hey, Bob Hoover's over there and he's friends with my mom. ... If meeting Bob for the first time or ANY time, the best way to deal with that is to NOT deal with it at all. ...
    (rec.aviation.military)
  • Re: Bob Hoover Short Story
    ... my advice is to practice what you'd say to him if you did meet ... of nowhere a colleague says "Hey, Bob Hoover's over there and he's ... if I might respectfully offer a bit of advice from ... The general consensus seems to be that celebrities sense immediately when people are being genuine or are simply "enchanted" to be talking with a famous person. ...
    (rec.aviation.military)
  • Re: Garden Yogurt, anyone tried it?
    ... > "Bob asked ... >> KG have tested it on an allotment plot and are convinced, so far, of it's ... > or even "humigro" on Google. ... > best practice combined with the fruits of experience will ensure results ...
    (uk.rec.gardening)
  • Re: New one - ED in diabetics
    ... I watch what I eat a lot better but ever since reading a 1994 ADA ... > Hi Bob ... > of the advice you've seen here to get those numbers down? ... > would be to concentrate on the basic cause - BGs control, ...
    (alt.support.diabetes)