Saving a file to desktop with VBA

  • Thread starter Thread starter Jeff Ciaccio
  • Start date Start date
J

Jeff Ciaccio

I have my user's putting in their name in cell B2, and I would like to set
the filename to B2 concat something. They will all be using Windows, so I
would like to set the directory to Desktop.

Can I automate this whenever the cell B2 changes? I'm using Excel 2000.

Thanks!!
 
I don't use exel 2000. th ecode below gets all the users files in the
persons desktop using the environmental variables in windows.

Sub test()

Home = Environ("HomePath")
Desktop = Home & "\" & "Desktop"


FName = Dir(Desktop & "\" & "*.*")
Do While FName <> ""
MsgBox ("Files : " & FName)
FName = Dir()
Loop

End Sub
 
Environ("HomePath") doesn't return the drive letter on my system, so if the
D: drive were the current drive, then your code would fail. On top of that,
I don't think the desktop is named Desktop universally (I think it is a
different "word" in different language editions of Windows, although I don't
know that for sure as I do not do any international programming); although I
am guessing this would probably not be an issue for the OP. However, here is
another way to get the full desktop path that I believe should work...

DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop")
 
Rick your code looks like is will solve my issue as well. I need to get the
"My Documents" environmental variable. The code below returns the default
windows location for My Documents, not where I have moved it to which is on
my D drive.

Dim sPathUser As String
sPathUser = Environ$("USERPROFILE") & "\My Documents\"
MsgBox sPathUser

Your code correctly sees that my Desktop is acutually on my D drive. I tried
yours by changing "Desktop" to "My Documents", but it doesn't return
anything, so the special folder name must be wrong?
DocsFolder = CreateObject("WScript.Shell").SpecialFolders("My Documents")
 
Strange as it seems given the argument is a quoted string, the correct
argument is MyDocuments without the space...

DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
 
Two reasons I can think of... first, the API solution should be (much)
faster than the method I posted (which could be important in a large loop)
and, second, some shops disable scripting run-times for security reasons.
 
Thank you again.

Rick Rothstein said:
Two reasons I can think of... first, the API solution should be (much)
faster than the method I posted (which could be important in a large loop)
and, second, some shops disable scripting run-times for security reasons.
Click to expand...
 
Back
Top