VBA code to save the location and name of a spreadsheet to a named range

[StevieMP]

Hi There,
I have some vba code that takes a "sheet" called 'BoAML_EDRCD' from an existing workbook and re-saves just that sheet as a new workbook to a location specified in the sheet. See code below:

So basically because I have put Path = "" , it will save the new workbook into the directory I happen to save the spreadsheet where I am running it from, which currently is what I want.
Then I want to take that filename and add the location to a named range in another sheet which I have the in the code:
ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = wb.FullName

Is there a way to strip the full path name down to just the last folder and filename it's saved in so that is saved to the range?
So instead of getting :
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - Test\filename.xlsx

I get just the :
\ ~ Pending - Test\filename.xlsx


Sub CreateBoAMLETDDelegatedReport2()

'This code will copy the "BoAML_EDRCD" sheet and rename/create a New Workbook and save it to the location specified
'in order to be sent to BoAML so they can start the ETD On-Boarding preocess.
'The name of the file can be seen in the sheet in cell B1.

Dim Path As String
Dim filename As String
Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim FName As Variant


Sheets("BoAML_EDRCD").Copy

Path = ""
'"G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\"
'"C:\Users\P3001951\Documents\"

filename = Range("AC2")

ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"


For Each wb In Application.Workbooks
ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = wb.FullName
Next

ActiveWorkbook.Close


End Sub

 

[mumps]

Try:

Sub CreateBoAMLETDDelegatedReport2()
    'This code will copy the "BoAML_EDRCD" sheet and rename/create a New Workbook and save it to the location specified
    'in order to be sent to BoAML so they can start the ETD On-Boarding preocess.
    'The name of the file can be seen in the sheet in cell B1.
    Dim Path As String, filename As String, wb As Workbook, wb2 As Workbook, v As Variant
    
    Sheets("BoAML_EDRCD").Copy
    Path = ""
    '"G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\"
    '"C:\Users\P3001951\Documents\"
    filename = Range("AC2")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
    For Each wb In Application.Workbooks
        v = Split(wb.Path, "\")
        ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = "\" & v(UBound(v) - 1) & "\" & v(UBound(v)) & ".xlsx"
    Next
    ActiveWorkbook.Close
End Sub

 

[StevieMP]

Try:

Sub CreateBoAMLETDDelegatedReport2()
    'This code will copy the "BoAML_EDRCD" sheet and rename/create a New Workbook and save it to the location specified
    'in order to be sent to BoAML so they can start the ETD On-Boarding preocess.
    'The name of the file can be seen in the sheet in cell B1.
    Dim Path As String, filename As String, wb As Workbook, wb2 As Workbook, v As Variant
   
    Sheets("BoAML_EDRCD").Copy
    Path = ""
    '"G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\"
    '"C:\Users\P3001951\Documents\"
    filename = Range("AC2")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
    For Each wb In Application.Workbooks
        v = Split(wb.Path, "\")
        ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = "\" & v(UBound(v) - 1) & "\" & v(UBound(v)) & ".xlsx"
    Next
    ActiveWorkbook.Close
End Sub

Hi Mumps,
It has saved it to the following path:
\P3001951\Documents.xlsx
rather than
\ ~ Pending - Test\filename

Sub CreateBoAMLETDDelegatedReport2()

'This code will copy the "BoAML_EDRCD" sheet and rename/create a New Workbook and save it to the location specified
'in order to be sent to BoAML so they can start the ETD On-Boarding preocess.
'The name of the file can be seen in the sheet in cell B1.

Dim Path As String
Dim filename As String
Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim FName As Variant
Dim v As Variant


Sheets("BoAML_EDRCD").Copy

Path = ""
'"G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\"
'"C:\Users\P3001951\Documents\"

filename = Range("AC2")

ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"


For Each wb In Application.Workbooks
v = Split(wb.Path, "\")
'- ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Cells ("filename") 'Value = "\" & "~ " & "Pending - Test\" & Folder & filename & ".xlsx"
'-- ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = wb.FullName
ThisWorkbook.Sheets("Sheet1").Range("BoAMLDelReport").Value = "\" & v(UBound(v) - 1) & "\" & v(UBound(v)) & ".xlsx"
Next

ActiveWorkbook.Close

 

[mumps]

I'm a little confused as to what you want to do. This line of code:

ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"

saves the file. What path folder does the variable "Path" represent? You have several lines of code commented out so I'm not sure if any of those lines are being used. Please post the actual code you are using without any lines that are not being executed. Also, explain clearly in more detail how you want to use the stripped down path name.

 

[StevieMP]

I'm a little confused as to what you want to do. This line of code:

ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"

saves the file. What path folder does the variable "Path" represent? You have several lines of code commented out so I'm not sure if any of those lines are being used. Please post the actual code you are using without any lines that are not being executed. Also, explain clearly in more detail how you want to use the stripped down path name.

Hi Mumps,
Thank you for having a look at the code.

Currently, the file where I run the code is saved in the following folder:
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - Test\ETDTest.xlsm

So, when I run the code from this file, it saves the new file (filename) created using the:
Path = ""
to here:
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - Test\filename.xlsx

What I want the code to do is save the file to the same destination folder:
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - Test\
but just giving the last folder and the filename only in the range specified.
So:
\ ~ Pending - Test\filename.xlsx

Your code is saving it to the :
C:\Users\P3001951\Documents\

Also, if I resave the ETDTest.xlsm to a new folder say
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - ABCD\ETDTest.xlsm
Then when I run the code it saves the new spreadsheet to the :
G:\DATA\BACKOFF\SETTLEME\Derivatives\ETD\Fund Launches\ ~ Pending - ABCD\filename.xlsx and populates the range in "Sheet1" with just the :
\ ~ Pending - ABCD\filename.xlsx

 

[mumps]

populates the range in "Sheet1" with just the : \ ~ Pending - ABCD\filename.xlsx

Is "Sheet1" the actual name of the sheet or are you referring to sheet "BoAML_EDRCD"? Also, is the range to be populated in the workbook containing the macro or in the newly created workbook? What exactly is the range that you want to be populated? Is there any reason why your code loops through the workbooks?

For Each wb In Application.Workbooks

 

[StevieMP]

Is "Sheet1" the actual name of the sheet or are you referring to sheet "BoAML_EDRCD"? Also, is the range to be populated in the workbook containing the macro or in the newly created workbook? What exactly is the range that you want to be populated? Is there any reason why your code loops through the workbooks?

For Each wb In Application.Workbooks

Hi Mumps,

I have a workbook called ETDTest.xlsm and there are several sheets, "Sheet1" is the actual name of the sheet. In this sheet there is a named range called "BoAMLDelReport".

Also within the ETDTest.xlsm workbook there is another sheet called "BoAML_EDRCD".

The code when run takes the sheet called "BoAML_EDRCD" and resaves this as a separate .xlsx spreadsheet and calls it the filename I have set within the sheet "BoAML_EDRCD" in cell "AC2". Lets call the filename TED

Once this has been saved, I want the range in ETDTest.xlsm "Sheet1" "BoAMLDelReport" to be populated withthe last folder and filename :
\ ~ Pending - Test\TED.xlsx

The loop does not really need to exist, so if there is a direct way that needs no loop then that would be ok.

 

[mumps]

Try this macro. Change the range (in red) that you want to be populated with the last folder and filename to suit your needs.

Sub CreateBoAMLETDDelegatedReport2()
    Dim Path As String, filename As String, wb As Workbook, v As Variant
    Set wb = ThisWorkbook
    Path = wb.Path
    v = Split(Path, "\")
    filename = Sheets("BoAML_EDRCD").Range("AC2")
    Sheets("BoAML_EDRCD").Copy
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
    wb.Sheets("Sheet1").Range("YourRangeHere").Value = "\" & v(UBound(v)) & "\" & filename & ".xlsx"
    ActiveWorkbook.Close False
End Sub

 

[StevieMP]

Try this macro. Change the range (in red) that you want to be populated with the last folder and filename to suit your needs.

Sub CreateBoAMLETDDelegatedReport2()
    Dim Path As String, filename As String, wb As Workbook, v As Variant
    Set wb = ThisWorkbook
    Path = wb.Path
    v = Split(Path, "\")
    filename = Sheets("BoAML_EDRCD").Range("AC2")
    Sheets("BoAML_EDRCD").Copy
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
    wb.Sheets("Sheet1").Range("YourRangeHere").Value = "\" & v(UBound(v)) & "\" & filename & ".xlsx"
    ActiveWorkbook.Close False
End Sub

Hi Mumps

Thank you. That worked after I amended your code on the line:
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
to:
ActiveWorkbook.SaveAs filename:=Path & "\" & filename & ".xlsx"

Really a big thank you!!!!!

 

[mumps]

You are very welcome.