VBA rename file when cell of another workbook is updated

On 12/31/2015 at 10:18

I have a file "cargoes 2016" as a database where the datas are sorted per "REF"(column "C"), I would like that when the cell of the colum "AI" is updated for a specific "REF", it goes to a specific folder and rename automatically the file whose name include "ref". However being totally new in vba, I don't know how to refer to the good cells/column.

Basically, if I update the cell of the column "AI" (=date) in the row of ref "2016xx001", the file in the specific folder who includes in its name "2016xx001" is automatically renamed (without opening)

what I did until now is create a module with the action "rename file" and insert code under the worksheet that if cell ai is updated, "rename file" macro runs

Here below both codes

Sub RENAME_FILES()

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

On Error Resume Next
For i = 2 To LR
DIRECTORY = "C:\Users\mciavaldini.TAMOILCY\Documents\test\"

REF = Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & i).Value

OldName = "CALC(P) - " & REF & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & ".xlsm"

NewName = "CALC(P) - " & REF & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & Format(Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI" & i).Value, "dd-mmm-yy") & ".xlsm"

Filename = Dir(DIRECTORY & "*" & REF & "*" & ".xlsm")

If Filename <> "" Then

   Name DIRECTORY & OldName As DIRECTORY & NewName
  End If

Next i
On Error GoTo 0
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To LR

If Not Intersect(Range("AI" & i), Target) Is Nothing And Range("AI" & i) <> "" Then

RENAME_FILES

End If

Next i
On Error GoTo 0

End Subcode

Please note that my table has around 100 rows, each "ref" being "2016xx001", 2016xx002, 2016xx003 etc

so if I update the column AI of the ref 2016XX003, then my code goes to find the file which includes "2016xx003" in its title, and rename it adding at the end of the name the column Ai value....

I know my problem is how to refer to the good cell....can someone help?

On 12/31/2015 at 13:51

Hello and welcome

To test

If not it must be the main file and the macros and 2-3 to rename files

Sub RENAME_FILES(Ligne As Long)
Dim LR As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

  DIRECTORY = "C:\Users\mciavaldini.TAMOILCY\Documents\test\"

  With Workbooks("CARGOES 2016").Worksheets("RECAP")
    LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    On Error Resume Next
    REF = .Range("C" & Ligne).Value
    OldName = "CALC(P) - " & REF & " - " & .Range("L" & Ligne).Value & " - " & .Range("M" & Ligne).Value & " + " & "CALC(S) - " & .Range("W" & Ligne).Value & " - " & .Range("X" & Ligne).Value & " - " & .Range("F" & Ligne).Value & "*.xlsm"
    Filename = Dir(DIRECTORY & OldName)
    If Filename <> "" Then
      NewName = "CALC(P) - " & REF & " - " & .Range("L" & Ligne).Value & " - " & .Range("M" & Ligne).Value & " + " & "CALC(S) - " & .Range("W" & Ligne).Value & " - " & .Range("X" & Ligne).Value & " - " & .Range("F" & Ligne).Value & Format(.Range("AI" & Ligne).Value, "dd-mmm-yy") & ".xlsm"
      Name DIRECTORY & Filename As DIRECTORY & NewName
    End If

    On Error GoTo 0
  End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Columns("AI"), Target) Is Nothing Then
    If Not IsDate(Target) Then Exit Sub
    RENAME_FILES Target.Row
  End If
End Sub
On 01/04/2016 at 07:45

it works perfectly, thank you so much

Just I replaced "ligne" by "row" so now the code is

Sub RENAME_FILES(row As Long)

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

DIRECTORY = "\\192.168.2.6\elenalouroutziati_document\B R E A K D O W N S\BREAKDOWNS 2016\"

With ThisWorkbook.Sheets("RECAP")

LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row

On Error Resume Next

REF = .Range("C" & row).Value

OldName = "CALC(P) - " & REF & " - " & .Range("L" & row).Value & " - " & .Range("M" & row).Value & " + " & "CALC(S) - " & .Range("W" & row).Value & " - " & .Range("X" & row).Value & " - " & .Range("F" & row).Value & ".xlsm"
Filename = Dir(DIRECTORY & OldName)

If Filename <> "" Then

NewName = "CALC(P) - " & REF & " - " & .Range("L" & row).Value & " - " & .Range("M" & row).Value & " + " & "CALC(S) - " & .Range("W" & row).Value & " - " & .Range("X" & row).Value & " - " & .Range("F" & row).Value & " - " & Format(.Range("AI" & row).Value, "dd-mm-yy") & ".xlsm"

Name DIRECTORY & Filename As DIRECTORY & NewName
End If

On Error GoTo 0
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Columns("AI"), Target) Is Nothing Then

If Not IsDate(Target) Then Exit Sub
RENAME_FILES Target.row

End If
End Sub
On 01/04/2016 at 11:43

Hello

Avoid using "row" because it is a reserved word by VBA

uses LRow

Sub RENAME_FILES(LRow As Long)

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

  DIRECTORY = "\\192.168.2.6\elenalouroutziati_document\B R E A K D O W N S\BREAKDOWNS 2016\"

  With ThisWorkbook.Sheets("RECAP")

    LR = .Cells.Find("*", searchorder:=xlByLRows, searchdirection:=xlPrevious).LRow

    On Error Resume Next

    REF = .Range("C" & LRow).Value

    OldName = "CALC(P) - " & REF & " - " & .Range("L" & LRow).Value & " - " & .Range("M" & LRow).Value & " + " & "CALC(S) - " & .Range("W" & LRow).Value & " - " & .Range("X" & LRow).Value & " - " & .Range("F" & LRow).Value & ".xlsm"
    Filename = Dir(DIRECTORY & OldName)

    If Filename <> "" Then

      NewName = "CALC(P) - " & REF & " - " & .Range("L" & LRow).Value & " - " & .Range("M" & LRow).Value & " + " & "CALC(S) - " & .Range("W" & LRow).Value & " - " & .Range("X" & LRow).Value & " - " & .Range("F" & LRow).Value & " - " & Format(.Range("AI" & LRow).Value, "dd-mm-yy") & ".xlsm"

      Name DIRECTORY & Filename As DIRECTORY & NewName
    End If

    On Error GoTo 0
  End With
End Sub
On 01/04/2016 at 13:15

thanks again

Search for threads similar to "vba rename file another workbook updated"