Automatically updating table
 

Hi,

In excel, there is a nice feture that allow to insert table from different
web pages. Is there a way to update at once all tables coming from web sites
without using data menu then refresh data for each table ? I have an excel
sheet that links 50 tables....

Thanks and regards
Jean Simon

Maybe something like this:

Sub Refr()
Dim QT As QueryTable
For Each QT In ActiveSheet.QueryTables
QT.Refresh
Next QT
End Sub

Hope this helps
Rowan

"Jean Simon" wrote:

Hi,

In excel, there is a nice feture that allow to insert table from different
web pages. Is there a way to update at once all tables coming from web sites
without using data menu then refresh data for each table ? I have an excel
sheet that links 50 tables....

Thanks and regards
Jean Simon

Hi Rowan,

Thanks. I feel very stupid, because I don't know what to do next.
Is this something that I need to put in vba ? How do I invoke this
subroutine.

Thanks and regards
Jean Simon

"Rowan" wrote:

Maybe something like this:

Sub Refr()
Dim QT As QueryTable
For Each QT In ActiveSheet.QueryTables
QT.Refresh
Next QT
End Sub

Hope this helps
Rowan

"Jean Simon" wrote:

Hi,

In excel, there is a nice feture that allow to insert table from different
web pages. Is there a way to update at once all tables coming from web sites
without using data menu then refresh data for each table ? I have an excel
sheet that links 50 tables....

Thanks and regards
Jean Simon

This is a VBA procedure.

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:

tools|macro|macros...
select the macro and click run.

======
You could even show the forms toolbar and put a button on the worksheet. Then
assign this macro to that button to let you just click a button to run it.

Jean Simon wrote:

Hi Rowan,

Thanks. I feel very stupid, because I don't know what to do next.
Is this something that I need to put in vba ? How do I invoke this
subroutine.

Thanks and regards
Jean Simon

"Rowan" wrote:

Maybe something like this:

Sub Refr()
Dim QT As QueryTable
For Each QT In ActiveSheet.QueryTables
QT.Refresh
Next QT
End Sub

Hope this helps
Rowan

"Jean Simon" wrote:

Hi,

In excel, there is a nice feture that allow to insert table from different
web pages. Is there a way to update at once all tables coming from web sites
without using data menu then refresh data for each table ? I have an excel
sheet that links 50 tables....

Thanks and regards
Jean Simon

--

Dave Peterson

Thank you, it worked fine.

Regards
Jean Simon.

"Dave Peterson" wrote:

This is a VBA procedure.

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:

tools|macro|macros...
select the macro and click run.

======
You could even show the forms toolbar and put a button on the worksheet. Then
assign this macro to that button to let you just click a button to run it.

Jean Simon wrote:

Hi Rowan,

Thanks. I feel very stupid, because I don't know what to do next.
Is this something that I need to put in vba ? How do I invoke this
subroutine.

Thanks and regards
Jean Simon

"Rowan" wrote:

Maybe something like this:

Sub Refr()
Dim QT As QueryTable
For Each QT In ActiveSheet.QueryTables
QT.Refresh
Next QT
End Sub

Hope this helps
Rowan

"Jean Simon" wrote:

Hi,

In excel, there is a nice feture that allow to insert table from different
web pages. Is there a way to update at once all tables coming from web sites
without using data menu then refresh data for each table ? I have an excel
sheet that links 50 tables....

Thanks and regards
Jean Simon

--

Dave Peterson