vba 保存excel文件的运行时错误1004（需要VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9751622/
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
Run time error 1004 for saving excel file (VBA required)
提问by user1204868
I was wondering if anyone knows how to use vbato save a .txtfile that is opened in excel?
我想知道是否有人知道如何使用vba来保存.txt在 excel 中打开的文件？
I have tried writing a coding with a UserForm, but it is giving me errors.
我曾尝试使用 UserForm 编写代码，但它给了我错误。
I was wondering if it is possible to give user the option to save it at his/her favourite spot, and also his/her favorite name?
我想知道是否可以让用户选择将它保存在他/她最喜欢的地方，以及他/她最喜欢的名字？
Public Sub CommandButton1_Click()
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
Dim CurrentFile As String
QuestionToMessageBox = "Do you want to save?"
YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Save file")
If YesOrNoAnswerToMessageBox = vbNo Then
Unload Me 'Cancellation command
Else
CurrentFile = ThisWorkbook.FullName
ActiveWorkbook.SaveAs "C:\myfile.xls", FileFormat:=52
Workbooks.Open CurrentFile
End If
End Sub
回答by Dick Kusleika
The error is because your file extension (xls) doesn't match your file type (OpenXMLWorkbookMacroEnabled). You would need the xlsm extension.
该错误是因为您的文件扩展名 (xls) 与您的文件类型 (OpenXMLWorkbookMacroEnabled) 不匹配。您将需要 xlsm 扩展名。
Sub Command1Click()
Dim lResp As Long
Dim sCurrFile As String
Dim sNewFile As String
Const sPROMPT As String = "Do you want to save?"
Const sFILTER As String = "*.xlsm, *.xlsm"
lResp = MsgBox(sPROMPT, vbYesNo, "Save File")
If lResp = vbYes Then
sCurrFile = ActiveWorkbook.FullName 'save current file name
sNewFile = Application.GetSaveAsFilename(, sFILTER) 'get new file name
If sNewFile <> "False" Then 'user didn't cancel
ActiveWorkbook.SaveAs sNewFile, xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close False 'close new file
Workbooks.Open sCurrFile 'open previous text file
End If
Else
Unload Me
End If
End Sub
回答by mkingston
I'm unsure why you're using Workbooks.Open after ActiveWorkbook.SaveAs. If the workbook is already open, isn't this unnecessary?
我不确定您为什么在 ActiveWorkbook.SaveAs 之后使用 Workbooks.Open。如果工作簿已经打开，这不是不必要的吗？
Anyway, to prompt the user for a save location try modifying the following as you require:
无论如何，要提示用户输入保存位置，请尝试根据需要修改以下内容：
Sub DoooooooooooooooooooIt()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
.Show
If .SelectedItems.Count > 0 Then
Debug.Print .SelectedItems(1)
End If
End With
End Sub
