Use VBA SaveAs in Excel
Ron de BruinExcel Automation
Use VBA SaveAs in Excel
Information
You see a lot of old SaveAs code that does not specify the FileFormat
parameter. In Excel versions before Excel 2007, code without this parameter
will not cause too many problems because Excel will use the current
FileFormat of the existing file and the default FileFormat for new files is
a (xls) in 97-2003 because there are no other Excel file formats before
Excel 2007. But because there are so many new file formats in Excel
, we shouldn't use code like this that does not specify the
FileFormat parameter. In Excel , SaveAs requires you to provide
both the FileFormat parameter and the correct file extension.For
example, in Excel , this will fail if the ActiveWorkbook is not an
xlsm fileActiveWorkbook.SaveAs &C:\ron.xlsm&
This code will always work
ActiveWorkbook.SaveAs &C:\ron.xlsm&, fileformat:=52 ' 52 =
xlOpenXMLWorkbookMacroEnabled = xlsm (with macro's in )
These are the main file formats in Excel , Note:
In Excel for the Mac the values are +151 =
xlOpenXMLWorkbook (without macro's in , xlsx)52
= xlOpenXMLWorkbookMacroEnabled (with or without macro's in , xlsm)
50 = xlExcel12 (Excel Binary Workbook in
without macro's, xlsb)56 = xlExcel8 (97-2003 format in
Excel , xls)Note: I always use the
FileFormat numbers instead of the defined constants in my code so that it
will compile OK when I copy the code into an Excel 97-2003 workbook (For
example, Excel 97-2003 won't know what the xlOpenXMLWorkbookMacroEnabled
constant is).
Below are two basic code examples to copy the ActiveSheet to a new
Workbook and save it in a format that matches the file extension of the
parent workbook. The second example use GetSaveAsFilename to ask you for a
file path/name. Example 1 you can use in Excel 97-2013 , Example 2 you can
use in Excel .If you run the code in Excel
will look at the FileFormat of the parent workbook and save the new file in
that format. Only if the parent workbook is an xlsm file and if there is no
VBA code in the new workbook it will save the new file as xlsx. If the
parent workbook is not an xlsx, xlsm or xls then it will be saved as xlsb.
If you always want to save in a certain format you can replace this part
of the macro:
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = &.xlsx&: FileFormatNum = 51
If .HasVBProject Then
FileExtStr = &.xlsm&: FileFormatNum = 52
FileExtStr = &.xlsx&: FileFormatNum = 51
Case 56: FileExtStr = &.xls&: FileFormatNum = 56
Case Else: FileExtStr = &.xlsb&: FileFormatNum = 50
End Select
With one of the one liners from this list
FileExtStr = &.xlsb&: FileFormatNum = 50 FileExtStr = &.xlsx&:
FileFormatNum = 51FileExtStr = &.xlsm&: FileFormatNum = 52
Or maybe you want to save the one worksheet workbook to csv, txt or
prn.(you can use this also if you run the code in Excel 97-2003)
FileExtStr = &.csv&: FileFormatNum = 6FileExtStr =
&.txt&: FileFormatNum = -4158FileExtStr = &.prn&: FileFormatNum = 36
Sub Copy_ActiveSheet_1()
'Working in Excel 97-2013
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
Set Sourcewb = ActiveWorkbook
'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) & 12 Then
'You use Excel 97-2003
FileExtStr = &.xls&: FileFormatNum = -4143
'You use Excel
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = &.xlsx&: FileFormatNum = 51
If .HasVBProject Then
FileExtStr = &.xlsm&: FileFormatNum = 52
FileExtStr = &.xlsx&: FileFormatNum = 51
Case 56: FileExtStr = &.xls&: FileFormatNum = 56
Case Else: FileExtStr = &.xlsb&: FileFormatNum = 50
End Select
'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
'Save the new workbook and close it
TempFilePath = Application.DefaultFilePath & &\&
TempFileName = &Part of & & Sourcewb.Name & & & & Format(Now, &yyyy-mm-dd hh-mm-ss&)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=False
MsgBox &You can find the new file in & & TempFilePath
With Application
.ScreenUpdating = True
.EnableEvents = True
Sub Copy_ActiveSheet_2()
'Working in Excel
Dim fname As Variant
Dim NewWb As Workbook
Dim FileFormatValue As Long
'Check the Excel version
If Val(Application.Version) & 9 Then Exit Sub
If Val(Application.Version) & 12 Then
'Only choice in the &Save as type& dropdown is Excel files(xls)
'because the Excel version is
fname = Application.GetSaveAsFilename(InitialFileName:=&&, _
filefilter:=&Excel Files (*.xls), *.xls&, _
Title:=&This example copies the ActiveSheet to a new workbook&)
If fname && False Then
'Copy the ActiveSheet to new workbook
ActiveSheet.Copy
Set NewWb = ActiveWorkbook
'We use the
format xlWorkbookNormal here to save as xls
NewWb.SaveAs fname, FileFormat:=-4143, CreateBackup:=False
NewWb.Close False
Set NewWb = Nothing
'Give the user the choice to save in
format or in one of the
'new formats. Use the &Save as type& dropdown to make a choice,Default =
'Excel Macro Enabled Workbook. You can add or remove formats to/from the list
fname = Application.GetSaveAsFilename(InitialFileName:=&&, filefilter:= _
& Excel Macro Free Workbook (*.xlsx), *.xlsx,& & _
& Excel Macro Enabled Workbook (*.xlsm), *.xlsm,& & _
Workbook (*.xls), *.xls,& & _
& Excel Binary Workbook (*.xlsb), *.xlsb&, _
FilterIndex:=2, Title:=&This example copies the ActiveSheet to a new workbook&)
'Find the correct FileFormat that match the choice in the &Save as type& list
If fname && False Then
Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, &.&, , 1)))
Case &xls&: FileFormatValue = 56
Case &xlsx&: FileFormatValue = 51
Case &xlsm&: FileFormatValue = 52
Case &xlsb&: FileFormatValue = 50
Case Else: FileFormatValue = 0
End Select
'Now we can create/Save the file with the xlFileFormat parameter
'value that match the file extension
If FileFormatValue = 0 Then
MsgBox &Sorry, unknown file extension&
'Copies the ActiveSheet to new workbook
ActiveSheet.Copy
Set NewWb = ActiveWorkbook
'Save the file in the format you choose in the &Save as type& dropdown
NewWb.SaveAs fname, FileFormat:= _
FileFormatValue, CreateBackup:=False
NewWb.Close False
Set NewWb = Nothingexcel vlookup函数使用方法
&&& 今天在登陆百度知道的时候，提示有人求助，看了问题，是关于excel中条件查找vlookup的问题，有几位高手都知道使用vlookup作答，可惜都是没有经过测试，直接复制别人的答案，让所有的读者都无法实施，一头雾水。今天我们详细解答一下vlookup函数的实际应用问题：vlookup函数的操作实例：如下图，已知表sheet1中的数据如下，如何在数据表二 sheet2 中如下引用：当A列学号随机出现的时候，如何在B列显示其对应的物理成绩？首先我们知道需要用到vlookup函数，那么先介绍一下使用 vlookup函数的几个参数，vlookup是判断引用数据的函数，它总共有四个参数，依次是：1、判断的条件2、跟踪数据的区域3、返回第几列的数据4、是否精确匹配根据以上参考，和上述在sheet2表的B列显示问题的实际需求，在sheet2表的B2单元格输入这个公式是：=vlookup(a2,sheet1!$a$2:$f$100,6,true)详细说明一下，在此vlookup函数例子中各个参数的使用说明：1、a2 是判断的条件，也就是说sheet1表和sheet2表中学号相同者，即sheet2表a列对应的数据和sheet1表中学号列a列的数据相同方能引用；2、sheet1!$a$2:$f$100 是数据跟踪的区域，因为需要引用的数据在f列，所以跟踪的区域至少在f列，sheet1!是不同表间引用所用的表名称，和标志是表间引用的!符号，$是绝对引用（关于），$a$2:$f$100 表明从A2到F100单元格的数据区域，如果数据区域不止100，那么可以直接使用A:F，这样虽然方便但是有风险，因为如果sheet1表的下方还有其它数据，就有可能出现问题；3、6 这是返回什么数的列数，如上图的物理是第6列，所以应该是6，如果要求英语的数值，那么此处应该是54、是否绝对引用，如果是就输入 true 如果是近似即可满足条件 那么输入false （近似值主要用于带小数点的财务、运算等）5、vlookup是垂直方向的查找，如果是水平方向查找可使用Hlookup函数，使用方法类似。结果如下图：不知道你是否已经会使用vlookup这个条件查找函数，如果你有兴趣可以试试本例。与本例结合紧密的是excel数据引用，更多的。
原创，原地址
转载请保留。
关于 excel vlookup函数使用方法 你可能还会阅读：
目前已有 0 条有价值评论
有图小站关注电脑、网络技术的分享，主要记录自己的工作和遇到问题的解决办法，少量转载他人高质量内容。如你觉本站有用，请收藏或告诉朋友一起看。想了解更多请点击。邮箱： QQ群：