I would like to open a new workbook from a folder through vba code written in a different macro. I would like to rename the opened file and save

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I need to perform same tasks on different workbooks every month. The files are present at different locations. I would like to create a macro file which will open the workbook from the folder that I define and then the very first thing it will do is rename the file by just replacing the last few characters. So every time, the file name will have "0_Original" in the end and the macro will replace it with "1_RetireeCheck" and save the file at the same folder defined earlier and then run the steps that I will define in the code. Below is the code I have written till now:

Sub EEBalanceSummary()
On Error Resume Next
Dim FilePath As String
dim wb as workbook
FilePath = Application.GetOpenFilename
if not FilePath = "False" then set wb = Application.workbooks.open(Filepath)
if wb is nothing then exit sub
with wb
.Activate
.Activesheet.Range("B6").Select
end with
End Sub

Right now, there is nothing about the renaming the file. It would be great if someone can help me with that and also make sure that the renamed file is open and active and I my vba codes will work on that renamed file(the one that ends with "1_RetireeCheck")
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,975
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi

So test the below on a dummy workbooks.

I think you will need to save as 1st, then delete the previous file.

so below we open the specified workbook
create the new name for the file and save as
then delete the original file we opened, leaving the new file open to manipulate

hope this helps

dave

VBA Code:
Sub open_and_rename()
mydir = "C:\Users\ 'add your directory
book_to_open = "db-0_Original.xlsm" 'choose book name to open


new_name = Replace(book_to_open, "0_Original", "1_RetireeCheck") 'creates new filename
Workbooks.Open Filename:=mydir & book_to_open 'opens the specified workbook
Workbooks(book_to_open).Activate 'activates the specified workbook
ActiveWorkbook.SaveAs mydir & new_name 'saves the workbook as the new name
Kill mydir & book_to_open 'deletes the original file


'now do what you require


End Sub
 

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave, thanks for the code; however, I need to open a file from a different folder and a different filename always but the ending of the filename will remain the same. The end part is what I replace every time. Also, I do not have to delete the original file
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,975
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi, ok so how yould you like to execute the part of entering the different filepath and name?

I had realised that is what you wanted to do, and that is why i created the code below
VBA Code:
mydir = "C:\Users\ 'add your directory
book_to_open = "db-0_Original.xlsm" 'choose book name to open

as for not deleting the file, just remove

VBA Code:
Kill mydir & book_to_open 'deletes the original file

Let me know how you would prefef to find your file for re-naming

browse to it?

Dave
 

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Dave,

Thanks for the reply. So I open the file choosing the folder for which I have the code. So the current code basically lets me choose the file and then the code makes the newly opened file active for me to do what I want in that file.

The changes that I want to make is when I open the file, I want to "save as" by replacing the end part of the name of the file as mentioned above and save it in the same location in which I opened the file that ends with "0_original". So basically now my file will be renamed with "1_Retireecheck" in the end that will be active and the one I would like to make changes in
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,975
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
ok

so the code below is assuming your current code has let you navigate to your file, open it and make it active.

you can just call this macro within your code when you are ready, or copy and paste the code into your macro at the correct point. losing the sub name and end sub of course



VBA Code:
Sub open_and_rename()
'So your code above that you have not posted should have now navigated to the file you want to re-name and save, and it is now active?
my_file = ActiveWorkbook.FullName'gets the current name and filepath of the current active workbook
new_name = Replace(my_file, "0_Original", "1_RetireeCheck") 'creates new filename by replacing as per your request.
ActiveWorkbook.SaveAs new_name 'saves the workbook as the new name

End Sub
 
Solution

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,975
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Sub EEBalanceSummary()
On Error Resume Next
Dim FilePath As String
dim wb as workbook
FilePath = Application.GetOpenFilename
if not FilePath = "False" then set wb = Application.workbooks.open(Filepath)
if wb is nothing then exit sub
with wb
.Activate
.Activesheet.Range("B6").Select
end with

my_file = ActiveWorkbook.FullName'gets the current name and filepath of the current active workbook
new_name = Replace(my_file, "0_Original", "1_RetireeCheck") 'creates new filename by replacing as per your request.
ActiveWorkbook.SaveAs new_name 'saves the workbook as the new name

End Sub
 

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
ok

so the code below is assuming your current code has let you navigate to your file, open it and make it active.

you can just call this macro within your code when you are ready, or copy and paste the code into your macro at the correct point. losing the sub name and end sub of course



VBA Code:
Sub open_and_rename()
'So your code above that you have not posted should have now navigated to the file you want to re-name and save, and it is now active?
my_file = ActiveWorkbook.FullName'gets the current name and filepath of the current active workbook
new_name = Replace(my_file, "0_Original", "1_RetireeCheck") 'creates new filename by replacing as per your request.
ActiveWorkbook.SaveAs new_name 'saves the workbook as the new name

End Sub
Thank you so much. It worked like charm :)
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,975
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
good to hear, you are welcome and thanks for your feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,566
Messages
5,770,893
Members
425,651
Latest member
Mark Cashin

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