EXCEL VBA - 根据范围值设置动态文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40349967/
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
EXCEL VBA - Setting a dynamic Filename based on range values
提问by DimWSH
I'm a VBA newbie attempting to save a filename based on range values. The information in the cells change everytime a Userform updates the information. I keep getting a Run-time error "1004" when the macro is run. This is the code I'm using:
我是 VBA 新手，试图根据范围值保存文件名。每次用户表单更新信息时，单元格中的信息都会更改。运行宏时，我不断收到运行时错误“1004”。这是我正在使用的代码：
ActiveWorkbook.SaveAs FileName:="C:\Users\ME\Desktop\TEST" & Sheets("Fail").Range("B8").Value & Sheets("Fail").Range("C8").Value & Sheets("Fail").Range("B10").Value & Sheets("Fail").Range("C10").Value & ".xlsx"
I have a feeling I'm formatting the code incorrectly. Any suggestions would be greatly appreciated!
我有一种感觉，我正在错误地格式化代码。任何建议将不胜感激！
EDIT: Adding more info: I'm extracting worksheets from another workbook and creating a temp file. I'm making the temp file the active workbook so I can save it to a different
编辑：添加更多信息：我正在从另一个工作簿中提取工作表并创建一个临时文件。我正在将临时文件设为活动工作簿，以便我可以将其保存到不同的
Worksheets("Fail").Visible = True
Worksheets("Fail Screenshot").Visible = True
tempFile = Environ("Temp") & "\Failed.xlsx"
Set wb = ThisWorkbook
wb.Save
wb.Sheets(Array("Fail", "Fail Screenshot")).Copy
Set tempWB = ActiveWorkbook
'TEST is the folder name on my desktop.
ActiveWorkbook.SaveAs FileName:="C:\Users\ME\Desktop\TEST" & Sheets("Fail").Range("B8").Value & Sheets("Fail").Range("C8").Value & Sheets("Fail").Range("B10").Value & Sheets("Fail").Range("C10").Value & ".xlsx"
采纳答案by SBF
Remove the extension ".xlsx" and add fileformat:=xlOpenXMLWorkbook
See https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
To have a workbook with macros, use fileformat:=xlOpenXMLWorkbookMacroEnabled
删除扩展名“.xlsx”并添加 fileformat:=xlOpenXMLWorkbook
请参见https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
要使用带有宏的工作簿，请使用 fileformat:=xlOpenXMLWorkbookMacroEnabled
回答by DimWSH
I ended up using the CONCATENATE function in a single cell within the active sheet to input the information from the multiple cells. I hid the column with the CONCATENATE function so the user won't see it and it won't print. The information generated from the CONCATENATE function will change based on user entry into a Userform. This is the code I used. Thanks to @SBF for the use of fileformat:=xlOpenXMLWorkbook. I used ".xlsx" originally and kept getting the compatibility popup notification.
我最终在活动工作表中的单个单元格中使用 CONCATENATE 函数来输入来自多个单元格的信息。我用 CONCATENATE 函数隐藏了列，这样用户就不会看到它，也不会打印。从 CONCATENATE 函数生成的信息将根据用户输入到用户表单而改变。这是我使用的代码。感谢@SBF 使用 fileformat:=xlOpenXMLWorkbook。我最初使用“.xlsx”并不断收到兼容性弹出通知。
Dim fileName As String
fileName = "C:\Users\ME\Desktop\TEST" & Sheets("Fail").Range("M7").Value & ".xlsx"
ActiveWorkbook.SaveAs fileName:=fileName, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
