Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    checking to see if query tables exist (2000)

    Hi all, in the following code I would like to check for the existence of a query table in each sheet. Otherwise the code runs great. There are 16 sheets in the book which import text files, and 5 or 6 rollup sheets. The code below checks all the sheets in the book.
    <pre>Sub PostFileDates()
    'Loops through sheets returns latest info date
    On Error Resume Next
    Dim oSheet As Worksheet
    Dim oBook As Workbook
    Dim strInfDate As Date
    Dim typInfDate As Date
    For Each oSheet In ThisWorkbook.Sheets
    <font color=red>'Check to see if QueryTable exists for this sheet</font color=red>
    oSheet.Activate
    For Each cell In oSheet.Range("b46:aj46")
    If cell.Value = 0 Then
    If Left(cell.Offset(-42, -1), 4) = "Week" Then
    strInfDate = cell.Offset(-42, -2).Value
    End If
    If Left(cell.Offset(-42, -1), 4) <> "Week" Then
    strInfDate = cell.Offset(-42, -1).Value
    End If
    GoTo 10
    End If
    Next cell
    10 MsgBox "Last date with information is " & strInfDate
    Next oSheet
    End Sub
    </pre>


    Also, I have seen few posts here in the lounge which use line numbers in code. Is this not a good practice, or are there some situations which just require line numbers? (It may be a subtle leftover from 1982 and Fortran..)

    Thanks,

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: checking to see if query tables exist (2000)

    Michael,

    Using ActiveSheet.QueryTables.Count should determine if there is a QueryTable on th eactive sheet, soo you coul start your code with<pre>For Each oSheet In ThisWorkbook.Sheets
    If If oSheet.QueryTables.Count > 0 Then ...</pre>

    Using numbers in code to facilitate the use of Goto is not good practice, and would be frowned upon these days.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •