Dynamic SaveAs

[Delta Star]

I'm setting up a file that will be distributed to many users. Once they have entered data into the file I would like the file to be saved directly to their desktop using the value in a sheet called "Data" cell H5.

I've found this code which shows the path of the users desktop. How can I amend it so that is saves the file to the route identified and is saved based on the cell value of the sheet called "Data" cell H5?

Const Desktop = &H10&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(Desktop)
strDeskPath = objFolder.Self.Path
MsgBox strDeskPath

 

[VoG]

Try like this

Const Desktop = &H10&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(Desktop)
strDeskPath = objFolder.Self.Path
ActiveWorkbook.SaveAs Filename:=strDeskPath & "\" & Sheets("Data").Range("H5").Value & ".xls"

 

[TiaXL]

na