Updating multiple workbooks in one folder

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
546
Office Version
  1. 365
Platform
  1. Windows
Good day. I am using Excel 2007 on windows Vista Business 32 bit. I have a folder containing 80 workbooks, all using the same format, each one being a different product from my store. I have used the INDIRECT.EXT function in these workbooks to allow me to reference values from closed workbooks. One of the workbooks being referenced contains product costs that are routinely updated from Quickbooks. Every time that I open one of the product workbooks, I can see that the values are being updated as the workbook opens. It has now become necessary to update all 80 of the product workbooks at one time to allow me to make changes to my selling prices.

Is there a way to open each workbook in the product folder, save the updated values and move on to the next workbook? I need to do this to each of the 80 workbooks in the folder.

As always, thanks for any help with this.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Give this a try, be sure to edit the fPath string to the correct folder.
Code:
Option Explicit

Sub OpenUpdateSave()
Dim fPath As String
Dim fName As String
Dim wb As Workbook
Dim Cnt As Long

Application.ScreenUpdating = False      'speed up macro
Application.DisplayAlerts = False       'no messages
fPath = "C:\2011\"                      'remember the final \ in path string
fName = Dir(fPath & "*.xls")            'list of files, get first filename

Do While Len(fName) > 0                 'one file at a time until done
    Set wb = Workbooks.Open(fPath & fName, True)    'open file, update links
    wb.Close True                                   'save and close
    Cnt = Cnt + 1
    fName = Dir                         'get next filename
Loop

If Cnt > 0 Then MsgBox "A total of " & Cnt & " workbooks were updated."
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Good day jbeaucaire. Thank you for responding to my request. I will try your solution and let you know what happens. I'm sure it will work.
Dan...
 
Upvote 0
Good day again jbeaucaire. Fantastic! Not only did it work, it did 166 files in 5 minutes. I didn't realize that there were that many files in the folder. I forgot that the folder also contains other products. My thanks to you for excellent help.

Dan...
 
Upvote 0
Good day once more jbeaucaire. Your help with my last issue was excellent. I posted another issue on March 10, titled "Macro help". It has fourteen views, but no replies. Could you look at it and tell me if what I want to do is possible. I'm not looking for a solution at this point. I just need to know if it can be done. If it's possible, then I'll try to work it out before I ask for help. If this is an issue that will require paid assistance, let me know that too.

Thanks, Dan...
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,337
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top