运行时错误1004用于保存excel文件（需要VBA） [英] Run time error 1004 for saving excel file (VBA required)

问题描述

我想知道有没有人知道如何使用 vba 保存在excel中打开的 .txt 文件？

I was wondering if anyone knows how to use vba to save a .txt file that is opened in excel?

我已经尝试使用UserForm，但它给我错误。

I have tried writing a coding with a UserForm, but it is giving me errors.

我想知道是否可以给用户选择保存在他/她最喜欢的地方，还有他/她最喜欢的名字？

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

推荐答案

错误是因为您的文件扩展名（xls）匹配您的文件类型（OpenXMLWorkbookMacroEnabled）。您将需要xlsm扩展名。

The error is because your file extension (xls) doesn't match your file type (OpenXMLWorkbookMacroEnabled). You would need the xlsm extension.

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

这篇关于运行时错误1004用于保存excel文件（需要VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！