在维护文件结构时强制另存为XLSM [英] Force Save as XLSM While Maintaining File Structure

问题描述

所以我正在使用XLTM文件，我希望用户确保他们保存为XLSM。当他们点击保存时，这样做不错，但是当他们点击另存为时，文件保存为* .xlsm.xlsm。我有点失落，如何确保用户保存为XLSM，同时保持文件名为filename.xlsm而不是filename.xlsm.xlsm。

 '动作确保用户保存为XLSM文件类型。 
 Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean，Cancel As Boolean）
 Dim FileNameVal As String 
如果SaveAsUI然后
 FileNameVal = Application.GetSaveAsFilename（Excel Macro-Enabled Workbook * .xlsm），* .xlsm）
取消= True 
如果FileNameVal =False然后'用户按取消
退出子
结束如果
 
 Application.EnableEvents = False 
 ThisWorkbook.SaveAs文件名：= FileNameVal& .xlsm，FileFormat：= ThisWorkbook.FileFormat 
 Application.EnableEvents = True 
 End If 
 End Sub 
  

我以为这个问题可能是写在.xlsm中：

  ThisWorkbook .SaveAs文件名：= FileNameVal& .xlsm，FileFormat：= ThisWorkbook.FileFormat 
  

但是，没有.xlsm我发现文件替代保存为一个坏的文件后缀。 （例如，如果我的XLTM文件称为Template（File001）.xltm，并且用户打开一个新的模板文件，它将保存为Template（File001）1（相信11是文件类型）。 p>

它可能是我的代码的结构，所以我需要指导如何修改它。

解决方案

问题似乎已经存在，因为该模板在实际上被称为Template（1）1最初保存这样会改变Excel保存文件的方式，所以最简单的方式来对比此初始保存和进一步保存（已经包含文件扩展名）之一是使用if-then语句来判断扩展是否已经存在。 >

  Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean，Cancel As Boolean）
 Dim FileNameVal As String 
如果SaveAsUI Then 
 FileNameVal = Application.GetSaveAsFilename（，Excel宏启用的工作簿（* .xlsm），* .xlsm）
取消= True 
如果FileNameVal = CStr（False）然后'用户按下取消
 Exit Sub 
 End If 
 Application.EnableEvents = False 
如果Right（ThisWorkbook.Name，5）<> .xlsm然后
 ThisWorkbook.SaveAs文件名：= FileNameVal& .xlsm，FileFormat：= xlOpenXMLWorkbookMacroEnabled 
 Else 
 ThisWorkbook.SaveAs文件名：= FileNameVal，FileFormat：= xlOpenXMLWorkbookMacroEnabled 
 End If 
 Application.EnableEvents = True 
 End如果
 End Sub 
  

So I am working with a XLTM file, and I want the user to make sure they save as XLSM. When they click "Save," this works fine, but I find when they click "Save As," the file is saved as "*.xlsm.xlsm". I am a little lost with how to make sure that the user saves as XLSM, while keeping the file name as "filename.xlsm" and not "filename.xlsm.xlsm".

    'Action makes sure the user saves as XLSM file type.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim FileNameVal As String 
    If SaveAsUI Then 
        FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm") 
        Cancel = True 
        If FileNameVal = "False" Then 'User pressed cancel
            Exit Sub 
        End If 

        Application.EnableEvents = False 
        ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat 
        Application.EnableEvents = True 
    End If 
End Sub 

I thought the problem may have been writing ".xlsm" in:

ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat 

However, without ".xlsm" written there, I find the file instead saves as a bad file suffix. (E.g., if my XLTM file is called Template(File001).xltm, and the user opens a new template file, it will save as Template(File001)1 (believing that "1)1" is the file type).

It may be the structure of my code, so I need direction in how to revise it.

解决方案

The problem appeared to have existed because the template would name the file "Template(1)1" prior to it actually being saved initially. This changes the way that Excel saves the file, so the easiest way to contrast between this initial save and further saves (that already contain a file extension) was to use an if-then statement to judge whether an extension exists already.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNameVal As String
If SaveAsUI Then
    FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
    Cancel = True
    If FileNameVal = CStr(False) Then 'User pressed cancel
        Exit Sub
    End If
    Application.EnableEvents = False
        If Right(ThisWorkbook.Name, 5) <> ".xlsm" Then
            ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Else
            ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    Application.EnableEvents = True
End If
End Sub

这篇关于在维护文件结构时强制另存为XLSM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！