Use VBA to make Excel refresh data from source

Question

My Excel sheet contains a table which fetches data from an outside source. I know I can refresh this data by right clicking the table and clicking "Refresh", but I want to be able to do this from inside my VBA code, so that I can force the data to be updated before performing certain calculations on it.

I have tried using Worksheets("Foo").Calculate as suggested by answers to similar questions, but that doesn't do the trick.

Try to record a macro and do the "Refresh". It's the best option for something like this. — Makah
– Makah, Commented Jan 13, 2014 at 13:54

thunderblaster · Accepted Answer · 2014-01-13 14:13:59Z

2

Should be able to use QueryTable("tableName").Refresh. Here is the link to the Microsoft Developer Network page on Query Table. Check out the refresh method from there.

edited Jan 13, 2014 at 14:13

answered Jan 13, 2014 at 13:46

thunderblaster

91811 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lee White Over a year ago

This seems like it should work, but VBA can't seem to find my table by its name. That seems to be a different problem though.

thunderblaster Over a year ago

If its your only table, you should be able to use QueryTable(1). If you have a lot of tables, finding its index number may be harder and you might be better off recording a macro as was suggested in the question comments.

Lee White Over a year ago

Worksheets("Foo").Range("B2").ListObject.QueryTable.Refresh BackgroundQuery:=False seemed to be the way to find my table. Strangely it wasn't even listed in QueryTable().

Collectives™ on Stack Overflow

Use VBA to make Excel refresh data from source

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related