Results for "adding variable txt file"

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?

Hello,

merged cells are sources of all kinds of difficulties.

My proposal to fix your issue.

ActiveSheet.Range("A10:C30").Find("Mr. G").Select

Hi all,

I've just started to learn about coding in VBA and I have an error in the following code that I don't know how to resolve.

I want to use a CheckBox, the first part If block is working as it should but the second one is not.

When I run the code the error 91 appear saying "Object Variable or With block variable not set".

ActiveSheet.Range("A10:A30").Find("Mr. G").Select

Does anyone know how to resolve this issue ?

Thank you !

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
ActiveSheet.Range("A10:A30").Find("").Select

Range(ActiveCell, ActiveCell.Offset(0, 2)).BorderAround _
ColorIndex:=1, Weight:=xlThin
Range(ActiveCell, ActiveCell.Offset(0, 2)).Merge
Range("A10:C10").BorderAround _
ColorIndex:=1, Weight:=xlMedium

ActiveCell.Value = "Mr. G"
ActiveCell.Font.Name = "Arial"
Range(ActiveCell, ActiveCell.Offset(0, 2)).HorizontalAlignment = xlCenter
Range(ActiveCell, ActiveCell.Offset(0, 2)).VerticalAlignment = xlCenter

End If

If CheckBox1.Value = False Then
ActiveSheet.Range("A10:A30").Find("Mr. G").Select
ActiveCell.ClearContents

End If

End Sub