Workbook Name

[kevinchurchill]

Hello
I have a master workbook template that many people use for a request form, as soon as they press SEND the origional form is kept on there local drive as a blank form.
However the sent file is saved on a network location with its own unique workbook name, My question is that on the worksheet within the book called "planning" I would like in Cell D2 the new name of the workbook to display and stay there for the duration of the form.
Is this possible using VBA, I have tried variouse methodes but with no luck.
Thank you in advance.

 

[Dr. Demento]

Re: Work book Name

Using the code linked to the SEND button, you must have some code that indicates the new file location. By appending that with the following, it should get you close to what you need.

Dim FileNewName as String

Set wbk = Workbooks("workbook.name")

With wbk
    [B][COLOR=#0000ff]FileNewName = .Sheets("Planning").Range("D2").value[/COLOR][/B]

    ' ~~ set file extension/format based on Excel 2007-2013
    strFileExt = ".xlsx": FileFormatNum = 51
    .SaveAs FilePath & [B][COLOR=#0000ff]FileNewName[/COLOR][/B], FileFormat:=FileFormatNum  ' or if that doesn't work, xlOpenXMLWorkbook 
    .Close SaveChanges:=False  ' This prevents the original from being overwritten
  End With

Where FilePath is already defined. Hopefully, most of the code I listed is already in place; only the blue/bold code would need to be added.

If you have more questions, please post the code that saves the workbook to the network location and we can go from there.

 

[kevinchurchill]

Hello and thank you very much for youre reply,
In fact I dont need the new file name to = D2, I need D2 to = the new file name generated by Excel which is something like (20190411171307.Xlsm).
I have tried to use =the GetBook() phrase but it won't automaticaly fetch the new name once saved on the network, here is the code im using to save the file.

Sub saveasandmailthelink()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

ActiveWorkbook.SaveAs FileName:="Z:\AMELIORATION_PROCESS\PUBLIC\Amélioration machine\DAM\Nouveau DAMS" & Format(Now(), "yyyymmddhhmmss") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


strbody = "<font size=""3"" face=""Calibri"">" & _
"Bonjour,<br><br>" & _
"J'ai rempli une demande d'amélioration, le fichier :<br><B>" & _
ActiveWorkbook.Name & "</B> Est créé.<br>" & _
"Cliquez sur ce lien pour ouvrir le fichier: " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & _
""">Lien vers le fichier</A>" & _
"<br><br>Cordialement." & _
"<br><br></font>"


On Error Resume Next
With OutMail

.To =
.CC = ""
.BCC = ""
.Subject = "Demande d'amélioration"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub


This code saves the file on the network and sends an e mail with the hyperlink to the new file to the end user

 

[Dr. Demento]

That’s actually a straight forward fix. Instead of defining the new name as you assign it to the file name, simply assign it to a string variable first. That way you can put it in the file name and D2.

Would that that work for you?

 

[kevinchurchill]

Thanks again for you’re reply I don’t really want to change the save format from this “yyyymmddhhmmss” as it gives me a unique number each time the book is saved and as this master form is on over 200 computers I really need a separate number for each save
If you have another idea I would be grateful.

 

[DanteAmor]

What mrdemento tried to say is that you put in a variable the name of the file, then put that data in cell D2 and then save the file:

Sub saveasandmailthelink()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim wPath As String, wFile As String
    
    wPath = "Z:\AMELIORATION_PROCESS\PUBLIC\Amélioration machine\DAM\"
[COLOR=#0000ff]    wFile = "Nouveau DAMS" & Format(Now(), "yyyymmddhhmmss") & ".xlsm"   'Put in a variable[/COLOR]
    
[COLOR=#008000]    ActiveWorkbook.Sheets(1).Range("D2").Value = wFile 'put in cell D2[/COLOR]
    
[COLOR=#800000]    ActiveWorkbook.SaveAs Filename:=wPath & wFile, _[/COLOR]
[COLOR=#800000]        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False 'save file[/COLOR]
    
    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        strbody = "" & _
        "Bonjour," & _
        "J'ai rempli une demande d'amélioration, le fichier :" & _
        ActiveWorkbook.Name & " Est créé." & _
        "Cliquez sur ce lien pour ouvrir le fichier: " & _
        " > Lien vers le fichier" & _
        "Cordialement." & _
        ""
        
        
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Demande d'amélioration"
            .HTMLBody = strbody
            .Display 'or use .Send
        End With
        On Error GoTo 0
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
End Sub

Let me know if you have any doubt.

 

[kevinchurchill]

After some initial confusion I now understand, this solution works perfectly for what I need and I thank you most sincerely.
Now i just need to close the newly created file that is presented to me when i have activated the macro, the original file is closed without saving changes this is perfect as it’s a user form but the saved file is left open, is it possible to close this also but with changes saved obviously.
Thank you.

 

[DanteAmor]

After some initial confusion I now understand, this solution works perfectly for what I need and I thank you most sincerely.
Now i just need to close the newly created file that is presented to me when i have activated the macro, the original file is closed without saving changes this is perfect as it’s a user form but the saved file is left open, is it possible to close this also but with changes saved obviously.
Thank you.

After this line

[COLOR=#333333]Set OutApp = Nothing[/COLOR]

Put this line

[COLOR=#333333]ActiveWorkbook.close false[/COLOR]

 

[kevinchurchill]

Thanks Dante, you’ve been extremely helpful, every thing is working as it should be.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.