How to open a list of excel files and get update, then close it?

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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

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


:

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