Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB editor not saving desktop | Excel Programming | |||
Saving to the desktop w/ VBA | Excel Programming | |||
Saving a workbook into the desktop | Excel Programming | |||
Desktop Saving in VB | Excel Programming | |||
VBA code for: Saving to desktop? | Excel Programming |