Saving to "MyDocuments" on users computer
 

I am writing a macro i want to be able to save a file to the users "My
Document/expired contracts". Not all users have the same path to My
Documents. Does VBA recognize %user% type commands? What would be the proper
syntax to make this work.
Thanks
Duane Reynolds

Saving to "MyDocuments" on users computer
 

You can find the "my documents" path with something like:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub


Duane Reynolds 323310 wrote:

I am writing a macro i want to be able to save a file to the users "My
Document/expired contracts". Not all users have the same path to My
Documents. Does VBA recognize %user% type commands? What would be the proper
syntax to make this work.
Thanks
Duane Reynolds

--

Dave Peterson

Saving to "MyDocuments" on users computer
 

You can also reference standard Windows XP profile folders for each user
using the following syntax:

Function GetProfilePath()
GetProfilePath = "C:\Documents and Settings\" & Environ$("username")& "\my
documents\"
End Function

Eugene



Duane Reynolds 323310 wrote:
I am writing a macro i want to be able to save a file to the users "My
Document/expired contracts". Not all users have the same path to My
Documents. Does VBA recognize %user% type commands? What would be the proper
syntax to make this work.
Thanks
Duane Reynolds

Saving to "MyDocuments" on users computer
 

"ekim" <u32520@uwe wrote in message news:6f408f66dbbca@uwe...
You can also reference standard Windows XP profile folders for each user
using the following syntax:

Function GetProfilePath()
GetProfilePath = "C:\Documents and Settings\" & Environ$("username")& "\my
documents\"
End Function

Eugene


Which is fine assuming a default install of windows with my documents in the
location you describe. I have many machines where 'My Documents' is stored
on a separate drive or partition and therefore not c:\

--
Chris Lewis

Saving to "MyDocuments" on users computer
 

Dave,
I gave this a try and obviously do not have the WScript set up as a
reference under the Tools, Ref Dialog -- What is the official name
in this program in this listing so that I can add it?

If I add this ref, will it add unnecessary overhead to my systems memory
everytime I load Excel - as I likely will not go further with the use of
the WScript program except for this example..?

Thanks,
jim

" wrote in message
:

You can find the "my documents" path with something like:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub


Duane Reynolds 323310 wrote:

I am writing a macro i want to be able to save a file to the users "My
Document/expired contracts". Not all users have the same path to My
Documents. Does VBA recognize %user% type commands? What would be the proper
syntax to make this work.
Thanks
Duane Reynolds

--

Dave Peterson

Saving to "MyDocuments" on users computer
 

Since the code uses late binding (declaring wsh as an object), then you don't
need a reference in your workbook's project.

Using early binding (declaring your objects as the correct type), makes
development easier--you get the intellisense that pops up to help.

But using late binding is probably better for deployment to others. You don't
have to worry about a reference to a different version causing trouble for users
(and you!).

Option Explicit
Sub testme()

Dim myDocumentsPath As String

'with a reference to Windows Script Host Object Model
Dim wsh As IWshRuntimeLibrary.WshShell
Set wsh = New IWshRuntimeLibrary.WshShell

'late binding/no reference
'Dim wsh As Object
'Set wsh = CreateObject("WScript.Shell")

myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

'maybe even add this when you're done getting your info:
set wsh = nothing

'rest of real code here

End Sub

As for overhead, there will be some--I don't know how much and how unnecessary.

If you search Google, you'll see API equivalent that probably have less overhead
(I'm way out of my element here!), but I wouldn't hesitate to this code.



JMay wrote:

Dave,
I gave this a try and obviously do not have the WScript set up as a
reference under the Tools, Ref Dialog -- What is the official name
in this program in this listing so that I can add it?

If I add this ref, will it add unnecessary overhead to my systems memory
everytime I load Excel - as I likely will not go further with the use of
the WScript program except for this example..?

Thanks,
jim

" wrote in message
:

You can find the "my documents" path with something like:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub


Duane Reynolds 323310 wrote:

I am writing a macro i want to be able to save a file to the users "My
Document/expired contracts". Not all users have the same path to My
Documents. Does VBA recognize %user% type commands? What would be the proper
syntax to make this work.
Thanks
Duane Reynolds

--

Dave Peterson

--

Dave Peterson