Querytables

Topic

New Reply

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?

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

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

Thanks,

Reply | Quote

Replies

Hi Michael,

What happens if you replace “ThisWorkbook” with “ActiveWorkbook”?

Ingrid

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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.

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

Reply | Quote