disabling queries or dataranges

[johnlengacher]

I have a large spreadsheet with many embedded queries pulling from an
access database that i want to publish to lots of different people.
how do i keep the queries from trying to refresh when those people open
the book, besides cutting and pasting all the values from one
spreadsheet into another one without data ranges? I want the
spreadsheet to ask me whether it should refresh on opening, and it
does, but i want to be able to disable this and send it on without
lot's of work.

excel 2000.

thanks.

 

[Ardus Petus]

Try this: it will prevent automatic refresh of your queries on open:

Sub DontQueryOnOPen()
For Each oSheet In ThisWorkbook.Worksheets
For Each oQT In oSheet.QueryTables
oQT.RefreshOnFileOpen = False
Next oQT
Next oSheet
ThisWorkbook.Save
End Sub

HTH,

 

[johnlengacher]

thanks for the help, but i don't really know how to use vba...there's no
command that will do this, ehh. the code you gave me, do i need to
replace any of it with the actual names from my worksheet?

 

[Ardus Petus]

"johnlengacher" <[email protected]>
a écrit dans le message de

thanks for the help, but i don't really know how to use vba...there's no
command that will do this, ehh. the code you gave me, do i need to
replace any of it with the actual names from my worksheet?

No, you can use it such as it is.

 

[johnlengacher]

hey thanks alot, i couldn't get it to work exactly the way you said, bu
i changed it so set EnableRefresh = False instead and it works like
charm.

problem solved