| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to open a list of excel files and get update, then close it?
I get 10 excel files, 1.xls, 2.xls, 3.xls, ... 10.xls, summary.xls
I need to open each file, get update and close it in ascending order. Does anyone have any suggestions to performance this tasks automatically through one action? if not, I have to update each file manually, which takes me 30 minutes to do that. Does anyone have any suggestions? Thank you in advance Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to open a list of excel files and get update, then close it?
Can you give us a few more details?
What "update" are you referring to? €¢Do all the sheets have the same structure? €¢Is there a single cell with a value that changed? €¢Are you copying a range from each sheet? €¢Do you skip sheets that did not change? €¢Are you comparing sheet values against a master sheet to detect changes? €¢Do the sheets use Named Ranges for the data you're interested in? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I get 10 excel files, 1.xls, 2.xls, 3.xls, ... 10.xls, summary.xls I need to open each file, get update and close it in ascending order. Does anyone have any suggestions to performance this tasks automatically through one action? if not, I have to update each file manually, which takes me 30 minutes to do that. Does anyone have any suggestions? Thank you in advance Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to open a list of excel files and get update, then close i
When I update the excel files, I mean update all the links within cells.
I don't need to type anything, Open the file Request for update the links within cells, which connected to another excel file containing all requested data. After updating the links, all formula within this spreadsheet will be re-calculated, and it takes sometimes [20 ~ 50s] for each files. Close the file. Do you have any suggestions? Thank you in advance Eric "Ron Coderre" wrote: Can you give us a few more details? What "update" are you referring to? €¢Do all the sheets have the same structure? €¢Is there a single cell with a value that changed? €¢Are you copying a range from each sheet? €¢Do you skip sheets that did not change? €¢Are you comparing sheet values against a master sheet to detect changes? €¢Do the sheets use Named Ranges for the data you're interested in? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I get 10 excel files, 1.xls, 2.xls, 3.xls, ... 10.xls, summary.xls I need to open each file, get update and close it in ascending order. Does anyone have any suggestions to performance this tasks automatically through one action? if not, I have to update each file manually, which takes me 30 minutes to do that. Does anyone have any suggestions? Thank you in advance Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to open a list of excel files and get update, then close i
One comment, one suggestion
Comment: I completely avoid linking workbooks. As the number of links increases, the workbooks become more and more fragile.....and eventually the entire structure fails. Every complex linked structure I ever inherited from other users became unstable/unusable in time. I can't recall one instance where the link to other workbooks were absolutely required and there was no workaround. Suggestion: You might be able to record a macro to open each workbook, refresh links, then save and close. That may save you some keyboardiing time. Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: When I update the excel files, I mean update all the links within cells. I don't need to type anything, Open the file Request for update the links within cells, which connected to another excel file containing all requested data. After updating the links, all formula within this spreadsheet will be re-calculated, and it takes sometimes [20 ~ 50s] for each files. Close the file. Do you have any suggestions? Thank you in advance Eric "Ron Coderre" wrote: Can you give us a few more details? What "update" are you referring to? €¢Do all the sheets have the same structure? €¢Is there a single cell with a value that changed? €¢Are you copying a range from each sheet? €¢Do you skip sheets that did not change? €¢Are you comparing sheet values against a master sheet to detect changes? €¢Do the sheets use Named Ranges for the data you're interested in? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I get 10 excel files, 1.xls, 2.xls, 3.xls, ... 10.xls, summary.xls I need to open each file, get update and close it in ascending order. Does anyone have any suggestions to performance this tasks automatically through one action? if not, I have to update each file manually, which takes me 30 minutes to do that. Does anyone have any suggestions? Thank you in advance Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to open a list of excel files and get update, then close i
Thank you for your suggestions
There is the code for Macro Workbooks.Open Filename:="C:\Files\1.xls", _ UpdateLinks:=3 ActiveWorkbook.Save ActiveWindow.Close The command "ActiveWindow" cannot be performed, do you have any suggestions on how change the code for specific file under specific directory, such as Save "C:\Files\1.xls" to replace the code ActiveWorkbook.Save Close "C:\Files\1.xls" to replace the code ActiveWorkbook.Close Thank you very much on any suggestions Eric "Ron Coderre" wrote: One comment, one suggestion Comment: I completely avoid linking workbooks. As the number of links increases, the workbooks become more and more fragile.....and eventually the entire structure fails. Every complex linked structure I ever inherited from other users became unstable/unusable in time. I can't recall one instance where the link to other workbooks were absolutely required and there was no workaround. Suggestion: You might be able to record a macro to open each workbook, refresh links, then save and close. That may save you some keyboardiing time. Does that help? *********** Regards, Ron XL2002, WinXP "Eric" wrote: When I update the excel files, I mean update all the links within cells. I don't need to type anything, Open the file Request for update the links within cells, which connected to another excel file containing all requested data. After updating the links, all formula within this spreadsheet will be re-calculated, and it takes sometimes [20 ~ 50s] for each files. Close the file. Do you have any suggestions? Thank you in advance Eric "Ron Coderre" wrote: Can you give us a few more details? What "update" are you referring to? €¢Do all the sheets have the same structure? €¢Is there a single cell with a value that changed? €¢Are you copying a range from each sheet? €¢Do you skip sheets that did not change? €¢Are you comparing sheet values against a master sheet to detect changes? €¢Do the sheets use Named Ranges for the data you're interested in? *********** Regards, Ron XL2002, WinXP "Eric" wrote: I get 10 excel files, 1.xls, 2.xls, 3.xls, ... 10.xls, summary.xls I need to open each file, get update and close it in ascending order. Does anyone have any suggestions to performance this tasks automatically through one action? if not, I have to update each file manually, which takes me 30 minutes to do that. Does anyone have any suggestions? Thank you in advance Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why excel close all files when I just want to close one files | Excel Discussion (Misc queries) | |||
| Cannot open files in Most Recently Used List | Excel Discussion (Misc queries) | |||
| fix for open/close files problem | Excel Discussion (Misc queries) | |||
| fix for open/close files problem | New Users to Excel | |||
| Hyperlinks to files open and then close instantaneously | Excel Discussion (Misc queries) | |||
Linear Mode
