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]

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")

 

[SQUIDD]

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

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]

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]

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

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

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]

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]

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

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

 

[SQUIDD]

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]

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

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]

good to hear, you are welcome and thanks for your feedback.