Dynamic Save Path

[Ravana]

Hi Friends

I am using the below code to save the particular worksheet of a workbook on my desktop. The thing here is, I am defining the path in the code itself. However, I do not wish to define the path where to save; instead, it should prompt asking where to save similar to "Save As" of the system.

Sub SaveFile()
Dim ans As Long
Dim sSaveAsFilePath As String

    On Error GoTo ErrHandler:
    
    sSaveAsFilePath = "C:\Users\V\Desktop\ECS.txt"

        If Dir(sSaveAsFilePath) <> "" Then
        ans = MsgBox("File " & sSaveAsFilePath & " exists.  Overwrite?", vbYesNo + vbExclamation)
        If ans <> vbYes Then
            Exit Sub
        Else
            Kill sSaveAsFilePath
        End If
    End If
    Sheet7.Copy '//Copy sheet 7 to new workbook
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextPrinter '//Save as Formatted Text (Space Delimited) file
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then '//Double sure we don't close this workbook
        ActiveWorkbook.Close False
    End If

My_Exit:
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub

Thanks in advance

 

[alansidman]

crossposted: Prompting path to save the file

 

[Ravana]

Hi alansidman

I apologize sincerely.