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.
Thanks in advance
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.
Code:
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