Saving spreadsheet with a Macro (VB)

[Jon Arbuckle]

Hi all. I am trying to write some VBasic code within Excel to save to the
path as follows:

C:\Documents and Settings\USERNAME\My Documents

I know in windows world you would use %HOMEDRIVE%%HOMEPATH%My Documents, but
I can't figure out the proper use within VBasic

 

[Bob Phillips]

MsgBox CreateObject("WScript.Shell").SpecialFolders(16)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Maybe something like one of these:

MsgBox Environ("userprofile")
MsgBox Environ("homedrive") & "\" & Environ("Homepath")

But I'd use:

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