+ Reply to Thread
Results 1 to 2 of 2

Change certain values within closed workbook

Hybrid View

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Change certain values within closed workbook

    I've saved this from one of JB's posts and it works well to update some values within a closed workbook, but only if the workbook is not password protected.

    Is there anything that can be done in this situation and also to not have to select enable macros for every workbook?

    Sub Update_WB_Values()
        Dim fPATH As String, fNAME As String, wb As Workbook
        
        fPATH = ThisWorkbook.Path & Application.PathSeparator
        
        Application.ScreenUpdating = False
        fNAME = Dir(fPATH & "*.xl*")
        
        Do While Len(fNAME) > 0
            If fNAME <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPATH & fNAME)
                With wb
                    With .Sheets("Application")
                        .Range("I11").Value = 12.43
                        .Range("J11").Value = 16.9794
                        .Range("I13").Value = 3.82
                        .Range("J13").Value = 6.3938
                    End With
                    .Close True
                End With
            End If
            fNAME = Dir
        Loop
        
        Application.ScreenUpdating = True
    End Sub
    HTH
    Regards, Jeff

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Change certain values within closed workbook

    Okay this seems to work.

    Sub Update_WB_Values()
        Dim fPATH As String, fNAME As String, wb As Workbook
        
        fPATH = ThisWorkbook.Path & Application.PathSeparator
        
        Application.ScreenUpdating = False
        fNAME = Dir(fPATH & "*.xl*")
        
        Do While Len(fNAME) > 0
            If fNAME <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPATH & fNAME)
                With wb
                    With .Sheets("Application")
                        .Unprotect "temp"
                        .Range("I11").Value = 12.43
                        .Range("J11").Value = 16.9794
                        .Range("I13").Value = 3.82
                        .Range("J13").Value = 6.3938
                        .Protect "temp"
                    End With
                    .Close True
                End With
            End If
            fNAME = Dir
        Loop
        
        Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1