Results 1 to 5 of 5

Thread: change cell value of closed workbooks (VBA)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Location
    Adelaide, South Australia
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default change cell value of closed workbooks (VBA)

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: change cell value of closed workbooks (VBA)

    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.

    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

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change cell value of closed workbooks (VBA)

    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.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: change cell value of closed workbooks (VBA)

    Quote Originally Posted by georgecomstock View Post
    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!

  5. #5
    Board Regular
    Join Date
    Apr 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change cell value of closed workbooks (VBA)

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •