Results 1 to 5 of 5

Thread: Querytables

  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

    Querytables

    Hi all,

    In my workbook, I am trying to set or change each query definition in each worksheet where it will not automatically update or prompt for a file name. In all, I guess to actually delete the query would be as an appropriate solution as any.

    I am trying to save a copy of the workbook with a different name - without the query trying to update automatically.

    Here is what I have so far (I have attempted several different versions of the syntax here and I keep getting the "Object does not support this property or method" message. Any ideas?

    <pre>Sub LocDown()
    Dim ws As Worksheet, qt As QueryTable
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerIncomingSalesSales.xls", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:=""
    For Each ws In ThisWorkbook.Sheets
    With ws.QueryTables
    .RefreshOnFileOpen = False
    End With
    Next
    Sheets("Totals").Select
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerPrevious Months" & ThisWorkbook.Sheets("Totals").Range("H1").Value, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:=""
    End Sub</pre>


    Please forgive the long lines, I cannot for the life of me figure out the underscore...

    Thanks,

  2. #2
    Lounger
    Join Date
    May 2001
    Location
    the Netherlands
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querytables

    Hi Michael,

    What happens if you replace "ThisWorkbook" with "ActiveWorkbook"?

    Ingrid

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querytables

    Do you have any chart sheets in your workbook? If so, replace thisworkbook.sheets with activeworkbook.worksheets.

    Exactly which line are you getting the error message on?

    Jon

  4. #4
    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: Querytables

    Hi Jon, Ingrid,

    I replaced ThisWorkbook with ActiveWorkbook - same result.

    I do not have any charts in the Workbook.

    The error happens on .RefreshOnFileOpen = False

    "The object does not support this property or method"

    Thanks,

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

    Querytables: This is what I did

    Ok, this is what I came up with. It saves itself, changes all the querytables in the workbook, then saves the workbook under the month and year name.



    <pre>Public Sub LocDown()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim fish As Date
    Dim catch As String
    fish = ThisWorkbook.Sheets("Totals").Range("H1").Value
    catch = MonthName(Month(fish)) & " " & Year(fish)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerIncomingSalesSales.xls", FileFormat:=xlNormal, _
    Password:=""
    For Each ws In ThisWorkbook.Sheets
    For Each qt In ws.QueryTables
    With qt
    .RefreshOnFileOpen = False
    End With
    Next
    Next
    Application.DisplayAlerts = True
    Sheets("Totals").Select
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerPrevious Months" & catch & ".xls", FileFormat:= _
    xlNormal, Password:=""
    End Sub
    </pre>


Posting Permissions

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