Results 1 to 3 of 3
  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 whether Query Table exists (2000)

    Hi all, I intend to use the following code at a future point to populate a listbox. The code as is works great with the exception that it checks all sheets in the book. There are 16 sheets which import text files, and 5 or 6 which are rollup sheets. I was trying to limit the code so it checks only those sheets with underlying queries. Any ideas on how to check to see if a query table exists or not?

    <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 here</font color=red>
    oSheet.Activate
    If oSheet.Range("a2").Value Is Not Date Then
    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
    End If
    10 MsgBox "Last date with information is " & strInfDate
    Next oSheet
    End Sub
    </pre>


    Also, I have seen very little code here in the lounge that uses line numbers to jump to different spots. Is there a better way to do the above, or are there some things that just need line numbers? This may be subtle memories or 1982 and Fortran coming back...

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking whether Query Table exists (2000)

    How about:

    For Each oSheet In ThisWorkbook.Sheets'Check to see if QueryTable exists for this sheet here
    oSheet.Activate
    If oSheet.Range("a2").Value Is Not Date Then
    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
    MsgBox "Last date with information is " & strInfDate
    exit for
    End If
    Next cell
    End If
    Next oSheet
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    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

    Re: Checking whether Query Table exists (2000)

    Thanks Kevin, didn't even know there was an "Exit For". Anyhow, I seem to inadvertently posted this same post in the Excel forum. Should I delete this one? I think I went back to edit one of the lines that I had added this morning to show what the sub was doing and I somehow posted it here and there. Pretty wild when I looked over here and saw I had posted it here too!

    Thanks,

Posting Permissions

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