PDA

View Full Version : Solved: Save Excel Workbook on users desktop



StigPepper
11-14-2006, 08:29 AM
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: -


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

lucas
11-14-2006, 08:36 AM
Try this but change the path to suit:
ActiveWorkbook.SaveAs FileName:="F:\Temp\BOM.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Run "checkname"

lucas
11-14-2006, 08:46 AM
For saving to the desktop specifically:

Sub SaveToDesktop()
Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & "Your File Name.xls"
End Sub

StigPepper
11-14-2006, 09:04 AM
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


FileName:=Sheets("Details").Range("R2")

Bob Phillips
11-14-2006, 09:23 AM
That is what Steve gave you



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

lucas
11-14-2006, 09:25 AM
This seems to work on a quick try:

Sub SaveToDesktop()
Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & Sheets("Details").Range("R2")
End Sub

lucas
11-14-2006, 09:26 AM
Hi Bob,
is the .value necessary?

StigPepper
11-14-2006, 09:34 AM
Thanks Steve & Bob,
Your advice worked a treat and saved me a whole lot of time and effort.
Thanks again

lucas
11-14-2006, 09:37 AM
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
11-14-2006, 01:31 PM
Hi Bob,
is the .value necessary?

Don't get me started Steve!

Only lazy, slobby programmers omit the default property :devil2:.

malik641
11-14-2006, 01:45 PM
Don't get me started Steve!

Only lazy, slobby programmers omit the defauly property :devil2:.
:rotlaugh:

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
11-14-2006, 02:09 PM
Ok, thanks guys for the honest replies:mkay every little bit helps.

mdmackillop
11-14-2006, 04:20 PM
Only lazy, slobby programmers omit the default property :devil2:.

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

lucas
11-14-2006, 04:25 PM
:rotlaugh: I knew there was an acronym for my skill level