Results for "vba rename file another workbook updated"

thanks again

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

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

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

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?

Can you create 2 datafiles with the real name and attach them here ?

benbenben89 a écrit :

At least the number of file will increase as in reallity 1No data file = 1No project

benbenben89 a écrit :

What I would like is to have 1No sheet located in the supervisor file by project

benbenben89 a écrit :

If I do a new project , that will create the table in a new supervisor sheet

benbenben89 a écrit :

When I update the data file , I can put a message box saying "supervisor file upfdated " ?

benbenben89 a écrit :

Where should I send you a postcard ?

Basically the 5No different files will be on a network at 5No different place (folder) .

At least the number of file will increase as in reallity 1No data file = 1No project .

What I would like is to have 1No sheet located in the supervisor file by project . If I do a new project , that will create the table in a new supervisor sheet ?

When I update the data file , I can put a message box saying "supervisor file upfdated " ?

Thank you !

Where should I send you a postcard ?

benbenben89 a écrit :

Now i need to found a way to automaticlly trace a graph with the evolution of the status A , status B and not commented (vertical axe) and date (horizontal graph)

benbenben89 a écrit :

i need as well now to ajust the code in order to integrate it into 5 different data file

    If Not Application.Intersect(Target, [a2:b2]) Is Nothing And Target.Count = 1 Then
.
.
.

        NoteAndDate = [a2:b2]
        Sttatus = Application.Transpose([b41:b43])

benbenben89 a écrit :

which will send into the supervisor file into 5 differents place .

that is exacly what i wanted !! thank you so much !

Now i need to found a way to automaticlly trace a graph with the evolution of the status A , status B and not commented (vertical axe) and date (horizontal graph)

i need as well now to ajust the code in order to integrate it into 5 different data file ( data file 1 , data file 2 , data file 3 , data file 4 ,data file 5) which will send into the supervisor file into 5 differents place .

Thank you very much

Ok. Here are the steps to successfully import datas in the supervisor file.

1- Create a file named supervisor.xls in the same directory as the datafile

2- In the data file, right-click on the sheet named table and click on "Visualize the code" or something like that

3- Paste the following code :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim chemin As String
    Dim NoteAndDate, Sttatus
    Dim SupFile As Workbook
    Dim derlign As Long

    If Not Application.Intersect(Target, [a2:b2]) Is Nothing And Target.Count = 1 Then
        Application.ScreenUpdating = False
        chemin = ThisWorkbook.Path & "\"
        NoteAndDate = [a2:b2]
        Sttatus = Application.Transpose([b41:b43])
        Workbooks.Open (chemin & "supervisor.xls") 'open the supervisor file if it is in the same directory as the datafile
        Set SupFile = ActiveWorkbook
        With SupFile.Sheets(1)
            derlign = .Range("a" & .Rows.Count).End(xlUp).Row + 1
            .Range("a" & derlign).Resize(, 2) = NoteAndDate
            .Range("c" & derlign).Resize(, 3) = Sttatus
        End With
        SupFile.Close True 'save and close the supervisor file
    End If
End Sub

4- Now change the value in A2 or B2 of the sheet table

5- Datas are imported in the supervisor file which is saved and closed.

5No means 5 quantity , sorry for the syntax

I have a question : what does "5No", "1No" mean ?

the data located in "sheet 1" take the information of the "table sheet" .Indeed , if you change in the "table sheet " the quantity of Status A , Status B etc etc , the quantity is sent to the "sheet1" . That is the first point .

Because I will have in total 5No different data file , I need what I called a supervisor file in order to summarise and get an history about all the information on 1No sheet.

Once I will update the A2 OR B2 located in the " table sheet" , I would like that in the "supervisor" file , 1No row is create and fill up automaticlly with the information located "sheet 1 table "

Ok.

On the sheet named Sheet1 there are 8 columns of datas. Which one do you want to put in the supervisor file ?

And there is always 1 row of datas (on row 5), am I alright ?

sorry excel 2007 ! I have the VBA editor

Simply because Microsoft decided not to implement VBA in excel 2008.

VBA with excel 2008 ; why that is not possible ?

Do you have excel 2008 for Mac ? If yes, using VBA is not possible.

Hi !

To do the step 2 , basically I am using the "connection fonction " located in the excel "data" menu . But may be the best way is to do everything in VBA.

My variable are

I have not create est the supervisor .xls file , as I do not know how to fill it automaticlly

"So with the datas of your pdf, if I change the 3 in the Note column, it automatically fills the supervisor file ?" : yes exacly

I am chasing the program

If you have any idea , please let me know

Hi benbenben89,

How do you do the step 2 ? With VBA ?

And basically, it's easier for us to help you if you attach an excel file rather a pdf. Tough your pdf is very clear.

And you say that you want the information in supervisor.xls to be filled automatically once the variable Note OR Date changed. Are you sure ? So with the datas of your pdf, if I change the 3 in the Note column, it automatically fills the supervisor file ?