excel - How to prevent my Save macro from running a second time
问题描述
I have a file that many employees use to determine pricing.
When they're done, they change the file name to whatever they would like.
Leadership has asked that we make it to where the first part of the employee's file save name is the file's original name (so we know the version they're using) then the second part is what the user wants it to be. Ex - File is org called "Rate Calculator v14". Employee wants to name the file "Los Angeles, CA - John Doe".
The file should then save in a specific folder as "Rate Calculator v14 Los Angeles, CA - John Doe.xlsm".
This should happen regardless of Save or Save As and regardless of whether it's done with the Ctrl+s shortcut or use of the ribbon.
When the last line of code is fired, the file is in fact saved, but the macro loops back around and asks the user once again what they want to save the file as (though it now won't be saved and the other name they used already saved).
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFileName(0 To 2) As String
strFileName(0) = "M:\Sales\Rate Calculators\John\"
strFileName(1) = "Rate Calculator v14"
strFileName(2) = InputBox("What would you like to save the file as?")
ActiveWorkbook.SaveAs FileName:=Join(strFileName), FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
I'm aware I also need to build in some error handling for use of incorrect characters in a file name, file name length, and determine the user so their file is saved within the correct folder.
解决方案
尝试
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFileName(0 To 1) As String, sPath As String
sPath = "M:\Sales\Rate Calculators\John\"
strFileName(0) = "Rate Calculator v14"
strFileName(1) = InputBox("What would you like to save the file as?")
ActiveWorkbook.SaveCopyAs Filename:=sPath & "\" & Join(strFileName) & ".xlsx"
End Sub
推荐阅读
- ibm-cloud-storage - 从 IBM Cloud 对象存储中读取对象的数据
- android - 在项目“:app”中找不到任务“installDebug”。仅在一个 react-native 项目中
- android - 尝试定义功能 (SAM) 接口时出错
- r - 如何修复散点图中某些变量的颜色
- jquery - 如何使用 JQuery 清除所有声明的变量
- java - 从 Docker 容器运行 MyBatis 迁移的最正确方法
- javascript - 如何打乱一个数组,在同一对象属性之间提供最大空间?
- javascript - 需要变量A来声明变量B,但是需要变量B来声明变量A
- pagespeed - 如何在本地主机上使用谷歌的页面速度洞察工具
- google-bigquery - 在大查询中按分隔符拆分