You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Excel Help - Macro to save file to a default location

4 replies [Last post]
Taha's picture
User offline. Last seen 12 years 7 weeks ago. Offline
Joined: 09/10/2008
Posts: 3

Hello there,

I need your kind help. I have created an excel spreadsheet that will be used by multiple users. Upon clicking the submit button, I want excel macro to save the file to a default location (without using the save as dialog box), thus users do not have to save the file themselves.

Thanks in advance for assistance
Taha

Lepa's picture
User offline. Last seen 2 years 2 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Excel Help - Macro to save file to a default location

Hi there,

Try the following function behind your save button. It will save the file as a .txt file in c:\temp\ and append today's date to it.

Sub save_default()

    ' file name
   filnm = "c:\temp\" + VBA.Strings.Format(Now, "yyyymmdd") + ".txt")
   
    ' Save file
   ActiveWorkbook.SaveAs Filename:=filnm, FileFormat:= _
        xlText, CreateBackup:=False
       
End Sub

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Taha's picture
User offline. Last seen 12 years 7 weeks ago. Offline
Joined: 09/10/2008
Posts: 3
Re: Excel Help - Macro to save file to a default location

Thanks so much my friend.
Just another question please. I created a button to activate the macro using thie code with slight modification. Can in some way this button could perform simultaneous actions i.e. Save the file to a default location and email it to the administrator as well?

Thanks again for your help.

Lepa's picture
User offline. Last seen 2 years 2 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Excel Help - Macro to save file to a default location

Taha, what's behind your button is "code" and you can do as many actions as you want. However, I'm not a VB expert and would have no idea how to code emails to get fired from an excel sheet. If you ever figure that out, I would appreciate it if you post is back on here for us to see!

Good luck!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Taha's picture
User offline. Last seen 12 years 7 weeks ago. Offline
Joined: 09/10/2008
Posts: 3
Re: Excel Help - Macro to save file to a default location

Thanks for reply. I will definitley let you all know when I have a solution

Cheers,