saving workbook with a macro

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,397
Office Version
  1. 2007
I want to save my workbook with a macro i can put on a button.

I'm writing the workbook in 2007 but it will be used by several novice users who have 2010 and i want to make saving easy and consistent for them.

I am constructing the full path and filename on one of the sheets thus:

A1: full path to folder on the users' network
A2: generic part of filename e.g. 'weekly diary'
A3: dynamic part of filename (the date for monday of the current week)
A4: file extension ( will be .xlsm to preserve macros
A5: full path and filename from concatenating the above e.g. 'C:\users\john\diaries/weeklydiary220914.xlsm'

this will cause diaries to auto-archive for previous weeks by using a new filename each week.

Please can anyone suggest macro code to do this? I assume it goes into a workbook module so it can be run from a button on any sheet?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try with an ActiveX button on the sheet containing the path etc

Code:
Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs Filename:=Range("A5").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
 
Upvote 0
my end users are on a corporate network and i think they'll have problems with active x.

Is there a way I can record a macro and then modify it with your code?

This is the code that came from my recorded macro:

Code:
Sub save()'
' save Macro
'


'
    ChDir "C:\Users\john\Dropbox"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\john\Dropbox\SESDTS jc 190914V1.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

i used the save-as dialogue and accepted all the setting as offered.
 
Upvote 0
Try

Rich (BB code):
Sub asave()
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A5").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Change Sheet1 to suit.
 
Upvote 0
Possibly another option...

Code:
Sub SaveToDiary()
    Dim sDay As String, sDay2 As String, sDay3 As String

    sDay = Date - Weekday(Date, vbMonday) + 1
    sDay2 = Replace(sDay, "/", "")
    sDay3 = Left(sDay2, Len(sDay2) - 4) & "14"

    ThisWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & _
                                  "\Diaries\" & "weeklydiary" & sDay3, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Please note that they do need a folder called Diaries in their username folder
 
Upvote 0
is there some way i can avoid or suppress the 'file already exists' warning message?
 
Upvote 0
Not sensible to suppress it if you are naming the file with the same name, which you are if you are running the code I posted in the same week as it will always name it with that Mondays date as you stated unless you want to over write the file (which you haven't said you wanted yet)
 
Last edited:
Upvote 0
Sorry, I should have said that yes I do want them overwritten. A dozen people will be changing it several times a day - maybe even at the same time - so it will need to be over written.
 
Upvote 0
Try putting Application.DisplayAlerts = False before the ThisWorkbook.save line and Application.DisplayAlerts = True before the End Sub line.
There is another way if it doesn't work for you but will have to wait until I am on my laptop rather than a phone.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top