Using macro, how do i refresh all worksheets in excel file?

Question

I have an excel file which is linked to another excel file stored on the network. using macro, i need to update/refresh this file at set intervals as the data in the source file is updated frequently. I am a beginner at this so please help

Answer 1

First of all, you did not specify which version of MS-Excel you are using. So I will assume it to be 2003 or earlier. Here is the basic information to refresh imported data.



Refreshing Data

--------------------------

• Automatically refresh data when a workbook is opened.



You can refresh an external data range automatically when you open the workbook, and optionally save the workbook without saving the external data, so that the workbook file size is reduced.



1. Click a cell in the external data range you want to refresh, and then click Data Range Properties on the External Data toolbar.

2. Select the Refresh data on file open check box.

3. If you want to save the workbook with the query definition but without the external data, select the Remove external data from worksheet before saving check box.



• Refresh multiple external data ranges.



1. If your worksheet contains more than one external data range that you want to refresh, on the View menu, point to Toolbars, and click External Data.

2. On the External Data toolbar, click Refresh All to refresh all of the external data ranges in the workbook.

If you have more than one workbook open, you must click Refresh All in each workbook to refresh external data.



• Refresh data in an imported text file.



1. Select the worksheet with your imported text file.

2. Click Refresh Data on the External Data toolbar.

3. In the Import Text File dialog box, select your text file, and then click Import. Tip: If you want to import only part of the data in a text file as a refreshable data range, you can create a query to retrieve the data. For more information, see Microsoft Query Help.



• Automatically refresh data at intervals.



1. Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar.

2. Select the Refresh every box, and then enter the number of minutes between refreshes.



• Control the order of refreshes.



1. Click a cell in the external data range you want to refresh first, and then click Refresh Data on the External Data toolbar.

2. Repeat for each external data range until they are all refreshed in the order you want.





Setting Refresh Options

-------------------------------------

• Require a password to refresh an external data range.



If your data source requires a password to connect to it, you can require that the password is entered before the external data range can be refreshed. This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy).



1. Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar.

2. Under Query definition, clear the Save password check box.

Notes:

- - - - • Stored passwords are not encrypted.

- - - - • Microsoft Excel prompts for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you will be prompted for the password again if you open the workbook that contains the query, and then click Refresh Data.



• Preserve cell formatting when you refresh an external data range



1. Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar.

2. Do one or both of the following:

- - - - • If you want to preserve cell formatting that you apply, select the Preserve cell formatting check box under Data formatting and layout.

- - - - • If you want to preserve column widths that you set, clear the Adjust column width check box under Data formatting and layout.

3. Click OK.

4. To refresh the external data range, click Refresh Data on the External Data toolbar.



• Run a query in the background or while you wait.



Running a query in the background allows you to use Microsoft Excel while the query runs.



1. Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar.

2. Select the Enable background refresh check box to run the query in the background or clear it to run the query while you wait.

Notes:

- - - - • Stop a query. To stop a query from running when Enable background refresh is turned off, press ESC.

- - - - • Stop a background refresh. To stop a query that is running in the background, double-click the Background Refresh icon on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

- - - - • Recording a macro. While you are recording a macro that includes a query, Excel will not run the query in the background. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True". For information about editing macros, see Visual Basic Help.



Note: You can check the status of a refr

Answer 2

Excel Macro Refresh Data

Answer 3

For the best answers, search on this site https://shorturl.im/ayjaz



I am going to assume you know VBA Macros fairly well. First you must mean all of the Excel files in the users directory so we will need to open all of them using the Dir Function with code roughly like sFileName = Dir$(USERSDirectoryPath/*.xls") do while len(sFileName) > 0 Workbooks.Open Filename:=USERSDirectoryPath _ + "/" + sFileName, ReadOnly:="True" sFileName = Dir$ Loop Once the workbooks are all open you can use code like the following to determine the contents of cell A5 in the first worksheet of each workbook. Dim oWorkbook As Object For Each oWorkbook In Application.Workbooks If oWorkbook.Name <> ThisWorkbook.Name Then MsgBox oWorkbook.Sheets(1).Cells(5, 1) End If Next Notes: The code for the Dir$ function has not been been tested but it is correct in general form. Points to remember are 1. You must use the entire path to find the files but the value returned is only the file name so you have to attach the path again to open the worksheet and 2. The Dir$ function inside the loop must not contain any arguments. You don't have to open the files as ReadOnly but I but your program could fail if you don't and one of the files is in use. The other code segment has been tested. You will want to do something other than looking at the cell value with MsgBox. If there are a large number of files involved you can change the code to open a file, find the A5 value, close that file, and proceed to the next. Whichever way you go you will want to be sure that you have started with only one workbook open. Hope this helps. You may expand on your question or contact me via Answers email if you have any questions. =================== ADDED 10/14/08 Ok, if you don’t know much about macros do the following Open the workbook you call “Output” Press Alt+F11 to open the VBAProject window Select Insert > Module to add a module to your project In the biggest area (Under the (General) dropdown list) paste the following code Public Sub CountA5() Dim sFileName As String sFileName = Dir$("C:/USERS/*.xls") Do While Len(sFileName) > 0 Workbooks.Open Filename:="C:/USERS/" _ + sFileName, ReadOnly:="True" sFileName = Dir$ Loop Dim oWorkbook As Object ThisWorkbook.Sheets(1).Cells(5, 4) = 0 ThisWorkbook.Sheets(1).Cells(5, 5) = 0 For Each oWorkbook In Application.Workbooks If oWorkbook.Name <> ThisWorkbook.Name Then If oWorkbook.Sheets(1).Cells(5, 1) = False Then ThisWorkbook.Sheets(1).Cells(5, 4) = _ ThisWorkbook.Sheets(1).Cells(5, 4) + 1 Else ThisWorkbook.Sheets(1).Cells(5, 5) = _ ThisWorkbook.Sheets(1).Cells(5, 5) + 1 End If End If Next End Sub Press Alt+F11 again to get back to the Excel window Press Alt+F8 to open the Macros window Ensure that CountA5 is selected and then select Options Enter q for the Shortcut key: and select OK Now any time you press Ctrl+q the Macro will run and refresh the counts in D5 and E5 of the first worksheet in “Output” workbook.

Answer 4

A macro is a task or set of tasks used frequently. Using a macro to update a file is a fairly simple operation.

Tools, Macro, Record new, Give the macro a name and assign a shortcut key. The box then minimises to just show the macro toolbar.

The macro records the keys pressed, in the sequence they are pressed and includes any mouse clicks and where they are. A macro also requires physical input from you as the macro will not just run on its own. If the spreadsheet you are working on references the data in the other file, then when you open the spreadsheet, you should be asked whether you wish to update the info or not. If you want to update, then simply click yes (update). This won't require the use of a macro at all.