Saving a XLS file through VBA

Question

I have created a macro that allows for a XLS file to send an email to the required person(s) and attach the XLS file.

How would I save the file to a location?

I have --- ThisWorkbook.SaveAs "testsave" & Format(Now, "dd-mm-yy") & ".xls"

But I wish to save the document to an internal web page other than on my D:.

I do not wish to create a new save, I would prefer it to overwrite the existing document.

Full code which is now saving a new doc and not overwriting the original

Sub sendemail()

'Save the form to the default location with todays date
Workbook.Save "HolidayReq" & Format(Now, "dd-mm-yy") & ".xls"

'Create the email and attach the form to it
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = "[""]"
    .cc = ""
    .BCC = ""
    .Subject = "New Holiday Request on " & Format(Now(), "dd/mm/yyyy") & " by " & Range("C2") & ""
    .Body = ""        .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Answer 1

You should just use Application.Save instead of SaveAs.

This will overwrite your original document

Application.ThisWorkbook.Save

If you want to save the file to a different path then you can append a path and use the original file's name with SaveAs

Application.DisplayAlerts = False
Application.ThisWorkbook.SaveAs "C:\Temp\" & ThisWorkbook.Name
Application.DisplayAlerts = True

(If you don't disable Alerts then you will receive a prompt if the file already exists.)

---

If you want to save your file as a different type, then you can use save as but specify a file type.

Application.ThisWorkbook.SaveAs ThisWorkbook.Path & "\test.html", xlHtml

Here is a list of the various Excel FileType constants