MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Save Query Definitions

  Asked By: Charlie    Date: Feb 08    Category: MS Office    Views: 782

How do I set this to false via VBA?

I cannot seem to find it by recording an import.



4 Answers Found

Answer #1    Answered By: Adalgisa Miller     Answered On: Feb 08

What on earth is this about? Please:

1) use your reply button when following up on a query  - that keeps the thread
2) include all the relevant material from previous posts so we don't have to
go searching for the context.

Could you repost this question with some context.

Answer #2    Answered By: Girja Garg     Answered On: Feb 08

I think it's to do with when you record a macro involving creating a
new web query, there is no difference in the code whether or not you
uncheck the option box 'Save query  Definition' (after the Import
button has been clicked an Import Data dialogue box appears which has
a Properties button which opens anopther dialogue box containing said
check box).

So if you run the recorded macro you end up with a saved query
regardless, as shown by a new defined Name and a refreshable query.

I found that if after
.Refresh BackgroundQuery:=False
I added the line
just before the
End With
statement it semed to have the desired effect of having the data but
not having a refreshable query.

However I was ofttimes left with a Defined Name (only a range) in the
workbook which is removable with
ActiveSheet.Names("www.yourqueryname").Delete after the
End With
Obviously replace "www.yourqueryname" with whatever the query is named
in the line
.Name = "www.yourqueryname"

Answer #3    Answered By: Tyler Thompson     Answered On: Feb 08

I am using the import  data query  to bring in 4 .wk1 files which are
constantly being replaced (with same file names) by another program.
The problem is that the query locks the source file preventing
replacement so I must eliminate the query after import and before
the files can be updated again.

I found this on another message board and it works great:

Sub Removequeries()
Dim ws As Worksheet
Dim qt As QueryTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
Next qt
Next ws
End Sub

Answer #4    Answered By: Sage Anderson     Answered On: Feb 08

I apologise for chastising you earlier! I thought you were referring to some
earlier query  and I could not make the connection.

Didn't find what you were looking for? Find more on Save Query Definitions Or get search suggestion and latest updates.