用于保存excel文件的运行时错误1004（需要VBA）

Question

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

我试过用UserForm编写代码，但它给了我错误。

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

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 

Answer 1

这个错误是因为你的文件扩展名（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 

Answer 2

我不确定您为什么使用Workbooks.Open后ActiveWorkbook.SaveAs打开。如果工作簿已经打开，这不是没有必要吗？

无论如何，以提示保存位置的用户尝试修改下面根据您的需要：

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