Open File using Macro-CodePudding

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.

You may download files from below path:

https://chandoo.org/forum/threads/open-file-using-macro.47347/

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

CodePudding user response：

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

CodePudding user response：

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