如何使用 excel vba 打开 .csv 并将其另存为 .xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22578566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use excel vba to Opening a .csv and saving it as .xlsx
提问by Leon
I am using MS Access form to open an .csv file using choose file.
我正在使用 MS Access 表单使用选择文件打开 .csv 文件。
Private Sub Import_Click()
Dim ExcelApp
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Workbooks.Open (Me.txtCSVFIle.Value)
ExcelApp.DisplayAlerts = False
ExcelApp.ActiveWorkbook.SaveAs FileName:=Me.txtCSVFIle.Value, FileFormat:=51
ExcelApp.Visible = False
ExcelApp.ActiveWorkbook.close False
ExcelApp.Quit
End Sub
the value of Me.txtCSVFIle.Value would be "I:\csv files\20140228_ExtStats.csv"
Me.txtCSVFIle.Value 的值将是“I:\csv files\20140228_ExtStats.csv”
Now the problem is my save as command is saving it as it's name with .csv. How can i remove the .csv so that my next statement FileFormat:=51 make it save to .xlsx?
现在的问题是我的另存为命令将其保存为 .csv 的名称。如何删除 .csv 以便我的下一条语句 FileFormat:=51 将其保存为 .xlsx？
*Ps: the Displayalerts is set to false to prevent user to be confuse.
*Ps：Displayalerts 设置为 false 以防止用户混淆。
Expected result should be save as "I:\csv files\20140228_ExtStats.xlsx".
预期结果应保存为“I:\csv files\20140228_ExtStats.xlsx”。
回答by Dmitry Pavliv
Try this one:
试试这个：
Private Sub Import_Click()
Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")
With ExcelApp
.Workbooks.Open (Me.txtCSVFIle.Value)
.DisplayAlerts = False
.ActiveWorkbook.SaveAs Filename:=Left(Me.txtCSVFIle.Value, InStrRev(Me.txtCSVFIle.Value, ".") - 1), FileFormat:=51
.Visible = False
.ActiveWorkbook.Close False
.Quit
End With
Set ExcelApp = Nothing
End Sub
