Get External Data from Web
in [Programming]
|
Prev: Advanced VBA Conditional Formatting & Field Population
Next: Set destination sheet based on variable and paste data assistance
From: Martin Parker on 13 Apr 2010 01:56 Hi I am pulling in currency exchange rate external data from a web site... no problems and storing the information on a worksheet. I have a userform with txtboxes that are linked to each of the cells. When the user form loads, the txtbox shows the currency exchange rate data imported in from the web site...agian no problems. I want to have a cmdbutton on the userform to update the data, i'm using:- ActiveWorkbook.RefreshAll but my question is the actual refresh will no take place until the userform is closed. How can i make the refresh data run whilst the userform is showing? Is this possible. I would like to click the command button to update the data within the worksheet and show the updated rates within each of the linked txtboxes on the userform without having to close the form down and re-open. Hope you can help. TIA
From: Martin Parker on 13 Apr 2010 02:58 To clarify a little further, I am using the following code to refresh data from a cmdbutton on a userform:- ActiveWorkbook.Connections("ConnectionCopper").Refresh However, the data is not refreshed until the userform is close. I would like to code to be updated whilst the userform is loaded. Is this possible? Cheers "Martin Parker" wrote: > Hi > > I am pulling in currency exchange rate external data from a web site... no > problems and storing the information on a worksheet. > > I have a userform with txtboxes that are linked to each of the cells. When > the user form loads, the txtbox shows the currency exchange rate data > imported in from the web site...agian no problems. > > I want to have a cmdbutton on the userform to update the data, i'm using:- > > ActiveWorkbook.RefreshAll > > but my question is the actual refresh will no take place until the userform > is closed. How can i make the refresh data run whilst the userform is > showing? Is this possible. I would like to click the command button to update > the data within the worksheet and show the updated rates within each of the > linked txtboxes on the userform without having to close the form down and > re-open. > > Hope you can help. > > TIA
From: om on 13 Apr 2010 08:05 Replace "Activeworkbook.Refreshall" with "Selection.QueryTable.Refresh BackgroundQuery:=False" This works exactly the way you wanted. Cheers Om :) "Martin Parker" wrote: > To clarify a little further, I am using the following code to refresh data > from a cmdbutton on a userform:- > > ActiveWorkbook.Connections("ConnectionCopper").Refresh > > However, the data is not refreshed until the userform is close. I would like > to code to be updated whilst the userform is loaded. Is this possible? > > Cheers > > > "Martin Parker" wrote: > > > Hi > > > > I am pulling in currency exchange rate external data from a web site... no > > problems and storing the information on a worksheet. > > > > I have a userform with txtboxes that are linked to each of the cells. When > > the user form loads, the txtbox shows the currency exchange rate data > > imported in from the web site...agian no problems. > > > > I want to have a cmdbutton on the userform to update the data, i'm using:- > > > > ActiveWorkbook.RefreshAll > > > > but my question is the actual refresh will no take place until the userform > > is closed. How can i make the refresh data run whilst the userform is > > showing? Is this possible. I would like to click the command button to update > > the data within the worksheet and show the updated rates within each of the > > linked txtboxes on the userform without having to close the form down and > > re-open. > > > > Hope you can help. > > > > TIA
From: om on 13 Apr 2010 08:06
Make sure you select at least one cell within your imported data before you run the macro. "Martin Parker" wrote: > To clarify a little further, I am using the following code to refresh data > from a cmdbutton on a userform:- > > ActiveWorkbook.Connections("ConnectionCopper").Refresh > > However, the data is not refreshed until the userform is close. I would like > to code to be updated whilst the userform is loaded. Is this possible? > > Cheers > > > "Martin Parker" wrote: > > > Hi > > > > I am pulling in currency exchange rate external data from a web site... no > > problems and storing the information on a worksheet. > > > > I have a userform with txtboxes that are linked to each of the cells. When > > the user form loads, the txtbox shows the currency exchange rate data > > imported in from the web site...agian no problems. > > > > I want to have a cmdbutton on the userform to update the data, i'm using:- > > > > ActiveWorkbook.RefreshAll > > > > but my question is the actual refresh will no take place until the userform > > is closed. How can i make the refresh data run whilst the userform is > > showing? Is this possible. I would like to click the command button to update > > the data within the worksheet and show the updated rates within each of the > > linked txtboxes on the userform without having to close the form down and > > re-open. > > > > Hope you can help. > > > > TIA |