使用宏打开文件
使用宏打开文件
提问于 2021-12-23 19:12:32
我只想用宏重命名一个文件夹中的两个文件，虽然该文件夹中有很多文件，但无法这样做。我想将文件名从"N2“重命名为"NORTH 2 (UP/UK)”，将"N3“重命名为"NORTH 3 (HR/PB)”。另外，我需要使用每个文件中第一列的动态范围，将名为"zone“的第一列中的所有值从"N2”重命名为"N2“到"NORTH 2 (UP/UK)”，将"N3“重命名为"NORTH 3 (HR/PB)”。
我只想点击一个按钮，这个任务想要自动化。编译器自动重命名文件，逐一打开每个文件，并按照上述条件替换现有的区域值，并保存更改。
我试过遵循代码，在你的团队的帮助下。但是，还是做不到。
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回答 2
Stack Overflow用户
发布于 2021-12-23 20:12:56
使用Workbooks.Open而不是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 SubStack Overflow用户
发布于 2021-12-23 22:57:38
有一个内置的VBA命令Name用于重命名文件(只要它位于本地驱动器或映射驱动器上，如示例中所示；对于UNC引用，您可以使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70466485
复制相关文章
相似问题
腾讯云开发者
