Save My Workbook with Two Different Names..

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Hello Excel Gurus,

How to save ThisWorkbook with two different names and locations please.

In cell H2 and H3 is the filename already been given in the sheet named HSheet of ThisWorkbook, generated via formula.

H2=
C:\Users\Kris Bryant\Share\2020\11-2020.xlsb

H3=
C:\Users\Kris Bryant\Share\2020\12-2020.xlsb
OR
H3=
C:\Users\Kris Bryant\Share\2021\01-2021.xlsb


You could see that not just the filenames are different but the locations can also be different for each file as well. However, the location of H3 does not exisit yet in windows explorer directory . So the code should be able to make one for saving the file inside that location. For example, 2021\
For H2 the location is already been made. And in case if the file already exists then overwrite it without any prompts. I hope it can be made possible.

Thanks and will appreciate a whole lot.
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
You may use the SaveCopyAs method...
If you already save the workbook using a macro, just add the line
VBA Code:
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("H3").Value

Otherwise you should consider adding a "BeforeSave" macro:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("H3").Value
End Sub
This code have to be inserted into the module "ThisWorkbook" of your vba project

Bye
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("H3").Value
Neat trick there. I am using your first code. What about the folder creation part? Will it be just as simple to create directory if it doesn't exist? Thanks as always.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Ok, it's a bit trickier; try with this snippet:
VBA Code:
'Declaration, at the beginning of the Sub
Dim lFso As Boolean, mySplit, FnPath As String


Set myFSO = CreateObject("Scripting.FileSystemObject")
FnPath = ThisWorkbook.Sheets("HSheet").Range("H3").Value
'
mySplit = Split(FnPath, "\", , vbTextCompare)
If myFSO.FolderExists(Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)) Then
    Debug.Print "OK---> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
Else
    MkDir Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
    Debug.Print "New--> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
End If
Set myFSO = Nothing
'Save Copy:
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("H3").Value
The Debug.Print lines are just for debuging; if it works ok then you can remove both the lines (but if they stay there it isn't a problem)

Bye
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Ok, it's a bit trickier; try with this snippet:
VBA Code:
'Declaration, at the beginning of the Sub
Dim lFso As Boolean, mySplit, FnPath As String


Set myFSO = CreateObject("Scripting.FileSystemObject")
FnPath = ThisWorkbook.Sheets("HSheet").Range("H3").Value
'
mySplit = Split(FnPath, "\", , vbTextCompare)
If myFSO.FolderExists(Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)) Then
    Debug.Print "OK---> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
Else
    MkDir Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
    Debug.Print "New--> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
End If
Set myFSO = Nothing
'Save Copy:
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("H3").Value
The Debug.Print lines are just for debuging; if it works ok then you can remove both the lines (but if they stay there it isn't a problem)

Bye

Thanks Anthony.
 

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