CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel - save file name with partially changed name

Good afternoon,

I would like to save the workbook under the partially changed string.

The name of my current file is:

DPP_EDI-1-PN66-SN8_28 Lochend Drive_EH7 6DJ_V1.0

and the full name remains the same. I just only want to change by end of the string from V1.0 to V2.0 or higher (based on the cell value).

I found some solution here: Trim last 4 characters from worksheet vba

and tried the following code:

  Sub Version_save()
  Dim wbn As ThisWorkbook
  If Right(wbn.Name, 4) = "V1.0" Then
  wbn.Name = Left(wbn.Name, Len(wbn.Name) - 4)
  End If

  End Sub

I am getting the error: I can't assign to a read-only property.

How can I save the new file with these small changes in my string?

Upvotes: 0

Views: 1875

Answers (2)

Applecore
Applecore

Reputation: 4099

I think that what you need to do is to "drop" the file extension from the end of the file name, and then remove the existing version number (in your current case "V1.0") and replace it with the new version from the worksheet (in my example I've used "V4.0" in cell "A1" of "Sheet1"), and then reattach the file extension:

Sub sChangeVersion()
    On Error GoTo E_Handle
    Dim strFileName  As String
    Dim strFileExt As String
    strFileName = ThisWorkbook.Name
    strFileExt = Mid(strFileName, InStrRev(strFileName, "."))
    strFileName = Left(strFileName, InStrRev(strFileName, ".") - 1) '   get rid of the file extension (it should be .xlsm)
    strFileName = Left(strFileName, InStrRev(strFileName, "V"))  '   now go back to the last "V" in the file name
    strFileName = strFileName & Format(ThisWorkbook.Worksheets("Sheet1").Range("A1"), "#0.0") & strFileExt
    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & strFileName
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sChangeVersiojn", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

I've modified the code to add just the number (formatted as "x.x") from the worksheet.

Regards,

Upvotes: 1

kyle campbell
kyle campbell

Reputation: 28

Messy but it works. Basically get the string of the old name and use Replace() to find piece of old string and swap with new value

Sub test()
Dim newVersion As String
newVersion = Sheets("Sheet1").Range("A1").Text
Dim wbn As String
wbn = ThisWorkbook.Name
MsgBox (wbn)
Dim wbn2 As String
wbn2 = Replace(wbn, "V1.0", newVersion)
MsgBox (wbn2)
End Sub

Upvotes: 0

Related Questions