VBA: How do you prevent a QueryTable from refreshing?

Question

I wrote a macro that imports a CSV file into my spreadsheet, using a QueryTable. My goal is to import the CSV data and be able to save it in the spreadsheet for future reference.

However, the QueryTable updates with the external CSV file, of course, and I would like to prevent this (because I want to forget about the external file). Is there a QueryTable setting I can use to prevent refreshes, or is this the wrong functionality to use altogether?

Answer 1

You can use VBA to just open the CSV as a workbook. Something like this.

Set myCSVbook = Workbooks.Open Filename:="C:\dir\myfile.csv", Format:=2 '2 is csv

Once you've done that, you can copy individual cells, or you could copy the whole sheet into another workbook in one line. I think there's a Copy method on the Worksheet object to do that.

Answer 2

Well.. if your goal is to have the data imported and not to keep up that data up to date (i.e. refreshing again with the CSV) then at the end of your VBA after .Refresh command for the query table, just delete the query table.

e.g.
Sheet1.QueryTables(1).Refresh (False)
Sheet1.QueryTables(1).Delete

Be sure to pass False to refresh command to ask Excel to do forground query, where your code execution will hold till you get your data in. Then your cell where you are adding the query table will be treated as regular cell.