Saving spreadsheet with a Macro (VB)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon Arbuckle - 08 Aug 2007 17:07 GMT

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

Reply to this Message

Bob Phillips - 08 Aug 2007 17:20 GMT

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

Signature

HTH

Bob

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

> Hi all. I am trying to write some VBasic code within Excel to save to the
> path as follows:
[quoted text clipped - 3 lines]
> I know in windows world you would use %HOMEDRIVE%%HOMEPATH%My Documents,
> but I can't figure out the proper use within VBasic

Reply to this Message

Dave Peterson - 08 Aug 2007 18:08 GMT

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

> Hi all. I am trying to write some VBasic code within Excel to save to the
> path as follows:
[quoted text clipped - 3 lines]
> I know in windows world you would use %HOMEDRIVE%%HOMEPATH%My Documents, but
> I can't figure out the proper use within VBasic

Signature

Dave Peterson

Reply to this Message

Rate this thread: