Forcing a file to save to any user's desktop

Firstly, an apology. I have no idea when it comes to VBA. I have through using screen recording created a macro which does what I need. From various posts I know how to get the output to save to a particular folder path and as I have left it below it saves to 'My Documents' which I guess is the default path.
I have looked at various posted solutions for making sure the saved file goes to the user's desktop irrespective of the pc it is being run on, but I just cant make things work. Also I would like a pop up message to appear and say "File Saved to Desktop".


I currently have the following code.

Please Login or Register  to view this content.

Would someone be kind enough to amend / add lines please.

Kind regards

Please Login or Register  to view this content.

Give this a go (amend the myPath as necessary)

Please Login or Register  to view this content.

Thank you so much for your very prompt replies.
I went down the responses and chose the first one that added to the code I already had, which was from Kalbasiatka.
It works perfectly.

Couldn't have done this without your support.

Many, many thanks to you all.

Could you also assist with the following related problem. I would like to add a folder to the desktop so that the output from the macro is saved in that folder to keep things tidy.
How I can add a folder say called CADEXPORT to the code from Kalbasiatka below. It doesn't need to actually create the folder, the user can do this manually.

Sub uuu()
Dim sFolder$, sFile$, sPath$
'--------------------------------
sFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop")
sFile = Range("c2") & ".xlsx"
sPath = sFolder & Application.PathSeparator & sFile
ActiveWorkbook.SaveAs sPath
MsgBox "File Saved to Desktop"
End Sub

Also looking for the additional code lines to create a pdf at the same time and save in the same Desktop folder CADEXPORT. So any help in giving me a complete block of code that I can use would be very much appreciated.

Apologies if I have asked these questions incorrectly ... not sure if I need to post / start a different thread for the above. If so please advise and I will do so.

Please Login or Register  to view this content.

Hi Kenneth

Appreciate your solution. It is virtually there. It is just that the .xlsx version gets saved with no file extension added. When I manually add the .xlsx it opens perfectly.
Can you help?

Many thanks
Pete

It works for me. Obviously, SaveAs an XLSX file leaves the file open and closes the XLSM file. If you need the XLSM opened and close the saved XLSX file, that can be done.

Of course xlOpenXMLWorkbook = 51. I prefer the former but that is up to you. As bakerman2 explained I used that feature for the XLSX and the PDF.

My usual practice is to also set the fileextension too but that requires a bit more code. 6 of 1 and 1/2 dozen of the other or is it "baker"s dozen?

Thank you both.
Not sure why the output file in the desktop folder is missing its .xlsx extension. I am really out of my depth with this and just relying on the good nature of you experts.

Kenneth, I do need the XLSM to remain open (which it does) and I need the created XLSX to be closed.
Could you possible amend the solution you posted earlier to help me out with that and also the missing file extension. Sorry to lean on you.

I do have one other final problem after that.
I created, by screen recording (all I am capable of) a second macro which with your code does what I need (bar the file extension issue I have). But when I run the second macro after first having successfully run the first one it errors. This is something to do with the fact that is expecting to access Book1 but Book2 is open? If I close it all down and start afresh the second macro runs fine. Not sure if my explanation is sufficient on this final problem?

Pete

As I cannot code, is the problem something to do with the screen recording. What I do is open a new workbook and then copy date from the .xlsm over into the new .xlsx which until I save it will have the name 'Book1'?

Just a quick update. Seem to have the error re the Book1 v Book2 sorted. Not sure what I have done. I re-recorded macro2 and whatever I have done seems to have sorted the issue. Similarly macro 2 puts the pdf as well as the file with the .xlsx extension in the desktop folder using the code you supplied. Still guessing how to fix the first macro which doesn't add the .xlsx file extension but uses your same code. grrrr

pulling my hair out!

Kenneth I have one macro working perfectly using the save code you wrote.
I have done a very simple macro with your save code that I cant get to work.

Please Login or Register  to view this content.

It stalls on the line....
ActiveWorkbook.SaveAs sPath, xlOpenXMLWorkbook '.xlsx = fileformat 51


Can you help me get this running please.

Sorry forget the last post.
Range ("C2") should have been C3
learning slowly.

At last... think I know what the problem is I am trying to save the file with the following naming convention...

CAD - 18.Aug to 17.Sep 2016

When I choose a purely text convention it saves perfectly with the .xlsx file extension added.

I need a way for the required naming convention to be accepted.

Can you help?