Saving to "MyDocuments" on users computer

[Duane Reynolds 323310]

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]

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

 

[ekim]

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

 

[Chris Lewis]

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:\

 

[JMay]

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

 

[Dave Peterson]

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.