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

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"

CodePudding user response：

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(3), fn, dwb As Workbook
    
    filelist(1) = "\\File1.xlsx"
    filelist(2) = "\\File2.xlsx"
    filelist(3) = "\\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.

CodePudding user response：

Your code is much more clever, but somehow it breaks in opening the very 1st spreadsheet:

"Error 1004 in runtime, we could not find it. Maybe you moved it?"

The actual address is very long, but I could copy, paste it on a Run command and open it, indeed. Maybe some lenght limitation?

\server101.se.net\dfs101\File_SE\EMEA\BCNUSRENER\E8\DATA\LTSA\Projects\LTSA IST\HE 1\1 ANALYSIS\FY2022\EA FY202212 - HE 1.xlsx

CodePudding user response：

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.