CODEWITHSUNDEEP
excelvba
Ravindra Bisht
Ravindra Bisht

Reputation: 3

Open File using Macro

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.

 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

Upvotes: 0

Views: 1526

Answers (2)

tinazmu
tinazmu

Reputation: 5139

There is a built-in VBA command Name for renaming files (as long as it is on a local or mapped drive like in your example; for UNC references, you can use the FileSystemObject):

Option Explicit

Sub FileOpen_Macro()
    Dim FileName(0 To 1) As String
    Const MyPath As String = "G:\Team Learning\vbapractice\Dunning\Export\"
    Dim strNewName As String, i As Long
    
    FileName(0) = "N2.xlsx"
    FileName(1) = "N3.xlsx"
    
    For i = 0 To 1
       strNewName = Replace(FileName(i), "N", "North-")
       Name MyPath & FileName(i) As MyPath & strNewName
       With Workbooks.Open(FileName:=MyPath & strNewName)
          ' Replace the cell A1 of the first sheet.
          .Worksheets(1).Cells(1, 1) = Replace(.Worksheets(1).Cells(1, 1), "N", "North-")
          ' and Save & close
          .Close SaveChanges:=True
       End With
    Next i

End Sub

Upvotes: 0

Vincent
Vincent

Reputation: 188

Use Workbooks.Open and not Workbook.Open.

Sub FileOpen_Macro()
Dim FileName(0 To 1) As String
FileName(0) = "N2.xlsx"
FileName(1) = "N3.xlsx"

For i = 0 To 1
Workbooks.Open FileName:="G:\Team Learning\vbapractice\Dunning\Export\" & FileName(i)
Next i

End Sub

Upvotes: 1

Related Questions