Clever Geek Handbook

Runtime error 1004 to save excel file (VBA required)

I was wondering if anyone knows how to use vba to save a file .txt

that opens in excel?

I tried writing encoding with UserForm but it gives me errors.

I was wondering if it is possible for a user to be able to save it in their favorite location as well as 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
+3


source to share


2 answers


The error is due to the file extension (xls) not matching your file type (OpenXMLWorkbookMacroEnabled). You will 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
+6


source


I'm not sure why you are using Workbooks.Open after ActiveWorkbook.SaveAs. If the workbook is already open, isn't that necessary?

Anyway, to prompt the user for the save location, try changing the following to suit your needs:



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
+4


source






More articles:


All Articles