change cell value of closed workbooks (VBA)

[sabi.kiss]

Hi there,

I have got a folder with 50 different workbooks, and I need to change cell B7 value in each one of them.
Is it possible to have a command button with a VBA to change the value of B7 in all these files, without having to open them?

Thanks,
Sabi

 

[Domenic]

The following macro will open each file within the specified folder, change the value in B7, save and close the workbook. Note that this is all done in the background. So you won't see the workbooks being opened, etc. Also, make the necessary changes, where indicated within the code.

Option Explicit

Sub test()


    Dim MyPath          As String
    Dim MyFile          As String
    Dim Wkb             As Workbook
    Dim Cnt             As Long
    
    Application.ScreenUpdating = False
    
    MyPath = "C:\Users\Domenic\Documents\MyFolder\" 'change the path accordingly
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xls")
    
    Cnt = 0
    Do While Len(MyFile) > 0
        Cnt = Cnt + 1
        Set Wkb = Workbooks.Open(MyPath & MyFile)
        Wkb.Worksheets("Sheet1").Range("B7").Value = "MyNewValue" 'change the new value accordingly
        Wkb.Close savechanges:=True
        MyFile = Dir
    Loop
    
    If Cnt > 0 Then
        MsgBox "Completed...", vbExclamation
    Else
        MsgBox "No files were found!", vbExclamation
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[georgecomstock]

Just dropping by to say thanks for the reply provided by Domenic. This small piece of code has saved me hours and hours of work. Just wanted to record my appreciation and to let you know that years after the original question your response is still helping people such as me.

 

[Domenic]

Just dropping by to say thanks for the reply provided by Domenic. This small piece of code has saved me hours and hours of work. Just wanted to record my appreciation and to let you know that years after the original question your response is still helping people such as me.

That's great to hear!

Cheers!

 

[jamesmev]

How would I adjust this code to replace a variable cell (based on another open workbook cell results)

 

[mg07p929]

@Domenic How would this code need to be adapted to make it work not only for one specific folder but for all files in all subfolders?