Saving a file to desktop with VBA
 

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

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")


"Rick Rothstein" wrote in message
...
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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

Strange as it seems given the argument is a quoted string, the correct
argument is MyDocuments without the space...

DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

--
Rick (MVP - Excel)


"MS Newsgroups" wrote in message
...
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")


"Rick Rothstein" wrote in message
...
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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

That did it of course. Thank you!

BTW, after I posted this message I stumbled across this VBA example on the
net and it also worked. I like your approach as it simpler and therefore
easier to work with. I'm curious as to why would anyone take this long
alternative approach?
http://en.kioskea.net/faq/sujet-951-...ment-variables

Ken

"Rick Rothstein" wrote in message
...
Strange as it seems given the argument is a quoted string, the correct
argument is MyDocuments without the space...

DesktopFolder =
CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

--
Rick (MVP - Excel)


"MS Newsgroups" wrote in message
...
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")


"Rick Rothstein" wrote in message
...
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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

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.

--
Rick (MVP - Excel)


"Ken" wrote in message
...
That did it of course. Thank you!

BTW, after I posted this message I stumbled across this VBA example on the
net and it also worked. I like your approach as it simpler and therefore
easier to work with. I'm curious as to why would anyone take this long
alternative approach?
http://en.kioskea.net/faq/sujet-951-...ment-variables

Ken

"Rick Rothstein" wrote in message
...
Strange as it seems given the argument is a quoted string, the correct
argument is MyDocuments without the space...

DesktopFolder =
CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

--
Rick (MVP - Excel)


"MS Newsgroups" wrote in message
...
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")


"Rick Rothstein" wrote in message
...
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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Saving a file to desktop with VBA
 

Thank you again.

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Ken" wrote in message
...
That did it of course. Thank you!

BTW, after I posted this message I stumbled across this VBA example on
the net and it also worked. I like your approach as it simpler and
therefore easier to work with. I'm curious as to why would anyone take
this long alternative approach?
http://en.kioskea.net/faq/sujet-951-...ment-variables

Ken

"Rick Rothstein" wrote in message
...
Strange as it seems given the argument is a quoted string, the correct
argument is MyDocuments without the space...

DesktopFolder =
CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

--
Rick (MVP - Excel)


"MS Newsgroups" wrote in message
...
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")


"Rick Rothstein" wrote in message
...
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("Desk top")

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"Jeff Ciaccio" wrote:

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!!


--
Jeff Ciaccio
Chemistry and Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio