Open File using macro

[rkbisht2019]

Hi All,
I wanna open filename using macro, but unable to do it. Although purpose of opening file is to rename files from "N2" to "North-2" and "N3" to "North-3". Also, renaming zone column which first column name in both excel files following same condition i.e. "N2" to "North-2" and "N3" to "North-3"

But I am stuck in Step-1, unable to open file using macro.
Also, if you can provide me working code for this requirement then I will be highly obliged to you.



Merry Christmas

>>> use code - tags <<<

    Sub FileOpen_Macro()
    Dim FileName(0 To 1) As String
    FileName(0) = "N2.xlsx"
    FileName(1) = "N3.xlsx"
  
    For i = 0 To 1
    Workbook.Open FileName:="G:\Team Learning\vbapractice\Dunning\Export\" & FileName(i)
    Next i
  
    End Sub

This thread should be open in VBA Macros (in this time moved).

 

[Logit]

https://stackoverflow.com/questions...e-files-and-perform-macro-on-all-opened-files

 

[rkbisht2019]

I wanna rename only two files from a folder using macro, Although there are lots files in that folder, but unable to do it. I wanna rename file name from "N2" to "NORTH 2 (UP/UK)" and "N3" to "NORTH 3 (HR/PB)". Also, I need to rename all values in first column named "zone" from "N2" to "from "N2" to "NORTH 2 (UP/UK)" and "N3" to "NORTH 3 (HR/PB)" using dynamic range of first column in each file.
I wish to just click on a button, and this task want to automate. Compiler automatically renames files, open each file one by one and replace existing zone values as per above condition and save the changes.
I have tried following code, by help of your team here. But, still unable to do it.
You may download files from below path: https://chandoo.org/forum/threads/open-file-using-macro.47347/

>>> as many times noted <<<
>>> use code - tags <<<

Option Explicit
Sub FileOpen_Macro()
    Dim FileName(0 To 1) As String
    Dim ReplaceName(0 To 1) As String
    Dim Name As String
   
    'Dim FileName As String
   
   
    Const MyPath As String = "G:\Team Learning\vbapractice\Import_N\"
    Dim strNewName As String, i As Long
   
    FileName(0) = "N2.xlsx"
    FileName(1) = "N3.xlsx"
   
    ReplaceName(0) = "NORTH 2 (UP/UK)"
    ReplaceName(1) = "NORTH 3 (HR/PB)"
   
    For i = 0 To 1
      
       strNewName = Replace(FileName(i), "N", ReplaceName(i))
      
       Name = MyPath & FileName(i)
       'With Workbooks.Open(FileName:=MyPath & strNewName)
       With Workbooks.Open(FileName:=Name)
          ' Replace the cell A1 of the first sheet.
          .Worksheets(1).Cells(2, 1) = Replace(.Worksheets(1).Cells(1, 1), FileName(i), ReplaceName(i))
          ' and Save & close
          .Close SaveChanges:=True
       End With
    Next i
End Sub

Ps. / won't work with filename!

 

[Marc L]

Hi, according to your initial attachment a VBA demonstration for starters :​

Sub Demo1()
  Const E = ".xlsx", P = "G:\Team Learning\vbapractice\Import_N\"
    Dim V, R&
        Application.ScreenUpdating = False
        V = Sheet1.[A1].CurrentRegion.Value2
    For R = 2 To UBound(V)
        If Dir(P & V(R, 1)) > "" Then
                 Name P & V(R, 1) As P & V(R, 2) & E
            With Workbooks.Open(P & V(R, 2) & E).Worksheets(1)
                .Range("A2:A" & .UsedRange.Rows.Count).Value2 = V(R, 2)
                .Parent.Close True
            End With
        End If
    Next
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​