vba to save workbook to a subfolder

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
Windows 8, Excel 2010; If I am working with any excel workbook and I run the command "ActiveWorkbook.Save", the workbook will be saved in the folder it came from. The workbook "knows" where it came from.

Is there a way to save workbook to a subfolder called "backup" that resides in the same parent folder as the original workbook? I don't want to describe the entire path because I want it to work on any computer so long as the parent folder has a subfolder called "backup". I want the routine to say "save it where you always do, except in the subfolder "backup".
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Here's something you can try.
You'll need to create a sub folder in the parent folder your workbook gets opened from, named BackUp .
Code:
Sub SaveInBackupSubfolder()

Dim MyName As String, MyPath As String
MyName = ThisWorkbook.Name
MyPath = ThisWorkbook.Path

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & MyName
If Err.Number = "1004" Then
  On Error GoTo 0
  GoTo JustSave
Else
  GoTo Quit
End If

JustSave:
ThisWorkbook.Save

Quit:
End Sub
It checks to see if the back up sub folder exists, and if so, does a SaveAs to it.
If it doesn't exist, it'll just save to the original folder it opened up from.
(This means you can do the save as, even if it was opened from the back up sub folder without any further monkeying around.)

Hope it helps.
 

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
Works smoother than butter! Thanks so much. That will save me a lot of time and effort.
 

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
When I incorporate your code into my routine I realize I made a mistake. After the file gets saved in "Backup" (which works perfectly) it needs to be resaved in the original folder (the "parent" folder). The idea is to leave an untouched version in a backup folder. My routine goes on and modifies the document in a dozen different ways and, as it is, that happens to the backup version and I need it to happen to the original. I'm sorry to impose on your generosity, but after saving it in the Backup could you have it resave in the original parent folder once again. (It would be resaving it over the original version, does that present an obstacle?)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Impose? Not at all. (We do this for fun!)
How about something like this?
Code:
Sub SaveInBackupSubfolder()

Dim MyName As String, MyPath As String
MyName = ThisWorkbook.Name
MyPath = ThisWorkbook.Path
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & MyName
If Err.Number = "1004" Then
  On Error GoTo 0
  MsgBox "The file path " & MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & " can not be found." & vbNewLine & _
         "Please make sure this file path exists before saving."
  Application.DisplayAlerts = True
  Exit Sub
End If
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & MyName
Application.DisplayAlerts = True
End Sub

Hope it helps.
 

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
Once again, it works perfectly. You make it look easy. Thanks so much.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Thanks for the kind words. You're more than welcome.
Glad it helped.
 

Pete2211

New Member
Joined
Mar 26, 2019
Messages
6
Hi.
I realize that this question was answered quite some time ago but it is the closest I could find to the question I would like to ask.

I want to automatically save a workbook to a specific subfolder. The name of the subfolder is always in the format 00 Month, so 01 Jan,02 Feb,03 Mar etc.
I want an opened copy of a workbook named 'Corrections ddmmyyyy'.xlsx to be saved into the subfolder corresponding to the mm of the file.
I'm sure I can get it to save but how can I get it to select the correct subfolder? I assume that the subfolder could be chosen numerically, rather than the text

Many thanks in anticipation.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top