I have a series of queries embedded in an excel spreadsheet, and a macro that refreshes each of these queries. The queries use the MS Query application, and are called by a series of statements like

    Range("C33").Select
        Selection.QueryTable.Refresh BackgroundQuery:=False

The procedure functions as intended. My problem is that whenever I close and re-open the worksheet, the data range properties for the query default back to 'insert entire rows for new data' as opposed to 'overwrite existing cells with new data', which is what I want to do.

I've tried recording a macro to set all the data range properties to 'overwrite', but this gives me a most unhelpful formula error message. The code looks like this:

     Range("C34").Select
            ExecuteExcel4Macro "(,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,0,TRUE,TRUE, FALSE)"

I need to either change whatever option sets the default to 'insert entire rows for new data' or fix the macro that resets the option in each individual location in the workbook. Can anybody help me do either?

Bryson Hadley

have you tried including the refreshstyle command?

Range("C33").Select
Selection.QueryTable.RefreshStyle = xlInsertEntireRows
Selection.QueryTable.Refresh BackgroundQuery:=False


cheers

Matt