Function 文件类型csv另存为xlsx()
'请记得先改一下路径,做好备份,最好不要另存为原路径
'ActiveWorkbook.SaveAs Filename:=Replace(ActiveWorkbook.Path, "新数据", "新数据-新") & "/" & Replace(ActiveWorkbook.name, "csv", "xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'这里是原路径,如果非要存原路径,也行,就用下面这行代码,把上面的注释掉
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "/" & Replace(ActiveWorkbook.name, "csv", "xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Function
Sub VBA打开文件夹内所有Excel表格_升级版_采用用户点选文件夹路径_而无须修改源代码()
Dim myPath$, myFile$, WB As Workbook '这个$ 是相当于定义字符串
'调用函数,获取用户选择的文件夹,并且在最后加上一个反斜杠,用于下面的文件列表获取
myPath = ChooseFolder & "\"
myFile = Dir(myPath & "*.*") '依次找寻指定路径中所有文件(所以文件夹里里面不要乱放其他文件)
Do While myFile <> "" '当指定路径中有文件时进行循环
If myFile <> ThisWorkbook.name Then '如果我们这个宏文件在需要处理的文件夹之中,这个判断就会跳过下面的操作
Set WB = Workbooks.Open(myPath & myFile) '打开符合要求的文件
Call 文件类型csv另存为xlsx
WB.Close 0 '不保存文件直接关闭
End If
myFile = Dir '找寻下一个*.xls,或者xlsx文件
Loop
Set WB = Nothing '释放变量内存
End Sub
Public Function ChooseFolder() As String
'定义并新建一个对话框对象
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
'如果当前没有对话框显示,就让他弹出对话框
If dlgOpen.Show = -1 Then ChooseFolder = dlgOpen.SelectedItems(1)
Set dlgOpen = Nothing
End Function