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

You don't need the file name if you're overwriting the old document. Just leave it out. — Sam
– Sam, Commented Dec 19, 2013 at 10:03

Sam · Accepted Answer · 2013-12-19 10:00:46Z

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

edited Dec 19, 2013 at 10:00

answered Dec 19, 2013 at 8:44

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3013325 Over a year ago

Its saving the document on my desktop and not saving over the original document.

Sam Over a year ago

What is, ThisWorkbook.Save?

user3013325 Over a year ago

is that not used to save the current workbook in its location?

Sam Over a year ago

@user3013325 Yes it is. But you don't need the file name.

Sam Over a year ago

@user3013325 You may want to try Application.SaveAs ThisWorkbook.Path & "\testsave" & Format(Now, "dd-mm-yy") & ".xls"

|

Collectives™ on Stack Overflow

Saving a XLS file through VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related