Solved: Save Excel Workbook on users desktop

[StigPepper]

Hi, this is my first post on your forum. Hopefully I am described my problem clearly, if you require further information please give me a shout.
Cheers, Stig.

I am using a VB macro on a survey designed on MS Excel. The survey is published on our work intranet and is set-up to return the completed survey to a central email account through the users Lotus Notes or Outlook account.

The problem I am experiencing is that before the form is returned it must be saved, and I have no control over where the form is saving using the code below.

Ideally I would like the survey to be saved on the users desktop, can you please advise how the code can be changed to allow this to happen: -

[vba]
ans = MsgBox("Do you want to make any more changes to the survey before sending?", vbYesNo)
If ans = vbYes Then
Exit Sub
End If
ThisWorkbook.SaveAs FileName:=Sheets("Details").Range("R2"), FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.RunAutoMacros (xlAutoClose)
FullPath = ThisWorkbook.FullName
FileName = ThisWorkbook.Name
[/vba]

[lucas]

Try this but change the path to suit:
[VBA]ActiveWorkbook.SaveAs FileName:="F:\Temp\BOM.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Run "checkname"[/VBA]

[lucas]

For saving to the desktop specifically:
[VBA]
Sub SaveToDesktop()
Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & "Your File Name.xls"
End Sub
[/VBA]

[StigPepper]

Hi Lucas,

Thanks for you help on this one I am going to try using the second line of code you suggested that saves the document to the users desktop. Can you advise how I can incorporate the code below into the filename.

Cheers

[VBA]
FileName:=Sheets("Details").Range("R2")
[/VBA]

[Bob Phillips]

That is what Steve gave you

[vba]

Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs Filename:=DTAddress & Sheets("Details").Range("R2").Value

[/vba]

[lucas]

This seems to work on a quick try:
[VBA]
Sub SaveToDesktop()
Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & Sheets("Details").Range("R2")
End Sub
[/VBA]

[lucas]

Hi Bob,
is the .value necessary?

[StigPepper]

Thanks Steve & Bob,
Your advice worked a treat and saved me a whole lot of time and effort.
Thanks again

[lucas]

Hi Stig,
glad it works for you....be sure to mark your thread solved using the thread tools at the top of the page.

[Bob Phillips]

Hi Bob,
is the .value necessary?

Don't get me started Steve!

Only lazy, slobby programmers omit the default property .

[malik641]

Don't get me started Steve!

Only lazy, slobby programmers omit the defauly property .

I've been explicitly using all default values since I found out that .NET requires you to use them (not to mention I, too, feel it's better programming practice).

[lucas]

Ok, thanks guys for the honest replies every little bit helps.

[mdmackillop]

Only lazy, slobby programmers omit the default property .

Forget MVP, looks like I'll just have to settle for LSP

[lucas]

I knew there was an acronym for my skill level