I want VBA to open without freeze a list of Excel spreadsheets to update a cell in each

Question

I want to open a list of spreadsheets to update a cell and save them again. But since it takes time to open each spredsheet, VB crashes after a few ones are open. Maybe using some timer to allow for more time to open each one? Or maybe some check to determine that the previous spreadsheet is already opened before proceeding with the next one? Many thanks!

    Workbooks.Open Filename:="\\File1.xlsx"
    ActiveWindow.Visible = False
    Windows("File1.xlsx").Visible = True
    Application.Goto Reference:="'Tab1'!R1C1"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "10/30/2022"
     
    Workbooks.Open Filename:="\\File 2.xlsx"
    ActiveWindow.Visible = False
    Windows("File2.xlsx").Visible = True
    Application.Goto Reference:="'Tab1'!R1C1"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "10/30/2022"

    Workbooks.Open Filename:="\\File3.xlsx"
    ActiveWindow.Visible = False
    Windows("File3.xlsx").Visible = True
    Application.Goto Reference:="'Tab1'!R1C1"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "10/30/2022"

Consider closing a files when you are done with it. Also look into looping and you want to [avoid suing select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Nov 04 '22 at 15:26

CLR · Answer 1 · 2022-11-05T08:31:02.767

0

Assuming you want to work from a list embedded in the code as you have right now, then this would get you going:

Sub updatecell()

    Const RangeToUpdate As String = "L1"
    
    Const TabToUpdate As String = "Tab1"
    
    Const ValueToUpdate As String = "10/30/2022"
    
    Dim filelist(2), fn, dwb As Workbook
    
    filelist(0) = "\\File1.xlsx"
    filelist(1) = "\\File2.xlsx"
    filelist(2) = "\\File3.xlsx"
    
    For Each fn In filelist
        Set dwb = Workbooks.Open(fn)
        dwb.Worksheets(TabToUpdate).Range(RangeToUpdate).Value = ValueToUpdate
        dwb.Save
        dwb.Close
    Next

End Sub

You might consider holding the list in a range on the workbook that holds the macro. Or - if the files are all in a folder and the filenames match a pattern - consider using Dir to locate each file in a loop.

edited Nov 05 '22 at 08:31

answered Nov 04 '22 at 15:55

CLR

11,284
1
11
29

I have been testing your code and Set dwb = Workbooks.Open(fn) doesn't seem to work, while Set dwb = Workbooks.Open("\File1.xlsx") does. Struggling to discover what the issue is. – Diego Rodríguez Peregrin Nov 04 '22 at 20:08
I've made an update. I forgot that the array holding filenames would start at 0. Give it a try now. – CLR Nov 05 '22 at 08:31
Yes, now it works perfectly opening, updating, saving and closing every spreadsheet. I will save me many hours each month on my many spreadsheets. Thanks a lot! – Diego Rodríguez Peregrin Nov 05 '22 at 09:34
@Diego You can delete comments that are misspelled or prematurely "saved" (with a hit on Enter). Like the first under this answer. Just move your mouse over the message and the word (actually button) `delete` will appear at the end. – Tom Brunberg Nov 05 '22 at 23:40

I want VBA to open without freeze a list of Excel spreadsheets to update a cell in each

1 Answers1