经常用到保存处理之后的Excel文件,保存的格式有xls和xlsx两种,这里记录一下保存代码,因为经常用到。不同版本下指定保存文件格式的常数略有不同,下面是2010版将已打开的文件保存为xlsx格式的代码:
'DatFile是打开的Excel文件名
expfile = ThisWorkbook.Path & "\new" & Left(DatFile, InStr(DatFile, ".") - 1)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=expfile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
如果保存xls格式(97-2003工作簿),则需要令FileFormat:=xlExcel8。
如果和原来的文件格式一样,只是换个文件名,不指定格式也是可以的,如下:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\new" & datfile
Application.DisplayAlerts = True
ActiveWindow.Close
下面的代码是我用来打开Excel文件或者固定格式的文本文件的函数:
'打开文件
Function OpenFile(fname As String) As Long
FullName = ThisWorkbook.Path & "\" & fname
If Dir(FullName, vbNormal) <> vbNullString Then
If Right(fname, 3) = "log" Then
Workbooks.OpenText Filename:=FullName, Origin:=936, StartRow:=1, DataType:=xlDelimited, Tab:=True
Columns("A:A").Select
Selection.NumberFormatLocal = "000000"
Columns("A:F").Select
Selection.Columns.AutoFit
Else
Workbooks.Open Filename:=FullName
End If
OpenFile = Range("A" & Cells.Rows.Count).End(xlUp).Row
Else
MsgBox "数据文件不存在!", vbOKOnly, "iamlaosong"
OpenFile = 0
End If
End Function