Plus, Excel shows a progress bar while it loads, instead of freezing up like with a Web Query.
That gives you full access to the data from the web request right after the call finishes.
'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination) Sub sb Copy Range To Another Sheet() 'Method 1 Sheets("Sheet1"). Download the Example VBA Macro File and Explore the code example to copy the data from one sheet to another worksheet: Copy Data Form One Sheet To Another Sheet Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation.
Valli is sharing useful VBA examples ad Tips to helps us automating daily tasks.
In the External Data Range Properties of your Web-Query you have a checkbox saying something like "Enable background refresh" which you should uncheck to achieve the desired effect.
Have a look at the bottom of this page: for pictures Edit: Here are two macros that show the desired effect: Sub Add Webquery() With Active Sheet. Add(Connection:= _ "URL; _ Destination:=Range("$A$1")) .
For more information on using the Cells go to the Cells property section.
Other topics included are Offset property, reading values between cells, reading values to arrays and formatting cells.
Quit Set app = Nothing Even though you've got your answer, for those that find this question, it is also possible to open an Excel spreadsheet as a JET data store. This is great if the Excel file your reading from is intended as a data source for validation.
Close False Set xl = Nothing End Sub Dim w As Workbooks Private Sub Workbook_Open() Application. Open Filename:="\server\Price List.xlsx", Update Links:=False, Read Only:=True 'this is the data file were going to be opening Active Window. By doing it this way though you will not get prompted to save changes back to the Excel file your reading from.
Screen Updating = True End Sub Private Sub Workbook_Before Close(Cancel As Boolean) w. Saved = True 'this will suppress the safe prompt for the data file only End Sub This is somewhat derivative of the answer posted by Ashok.