Excel IQY file - Usage Option question

Question

I have a simple Excel IQY file in which the contents look like this:

WEB
1
http://somesite.html

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

This is simple, no problems.

What I need to do is add options that will turn ON the "Refresh data when opening the file" and turn ON "Remove data from the external data range before saving the workbook".

I find these settings when looking at the connection properties under the "Usage" tab, but, I can't find where (or if) I can set these in the IQY file so that these two options are check on by default when using the iqy file.

Thanks!

Answer 1

I tried an example with existing IQY file & found that those are properties of the DataRange and not the query. So, it is stored as properties of the QueryTable

Assume that you have 1 QueryTable in the Excel Sheet. Here is the VBA code that can be used to read/set the properties, you are looking for.

Press ALT + F11 (you will see the VBA editor)
Press CTRL + G (immediate window)
Type in the following statement one by one & see what it has to say

Msgbox ActiveSheet.QueryTables(1).RefreshOnFileOpen
Msgbox ActiveSheet.QueryTables(1).SaveData

And then, try & set those properties from the code and see the effect of it in the DataRange properties dialog box.

ActiveSheet.QueryTables(1).RefreshOnFileOpen = True
ActiveSheet.QueryTables(1).SaveData = True

Hope that helps.