Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Saving from a Macro (2003 SP1)

    How do I write a macro that will save to My Documents that will work on different machines or for different users (where the path to My Documents may not be the same).

    Can I use the environment variable USERPROFILE to do this? If so, how?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving from a Macro (2003 SP1)

    Copy the following code at the top of a module:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Public Const CSIDL_PERSONAL = &H5
    Private Const NOERROR = 0

    Private Declare Function SHGetSpecialFolderLocation Lib "shell32" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    ppidl As Long) _
    As Long

    Private Declare Function SHGetPathFromIDList Lib "shell32" _
    (pidl As Long, _
    ByVal pszPath As String) _
    As Long

    Private Declare Sub CoTaskMemFree Lib "ole32" _
    (ByVal pv As Long)

    Private Const MAX_PATH = 260

    Function GetSpecialFolderLocation(ByVal lngCSIDL As Long) As String
    Dim lngRet As Long
    Dim strLocation As String
    Dim pidl As Long

    lngRet = SHGetSpecialFolderLocation(0, lngCSIDL, pidl)
    If lngRet = NOERROR Then
    strLocation = Space$(MAX_PATH)
    lngRet = SHGetPathFromIDList(ByVal pidl, strLocation)
    If Not lngRet = 0 Then
    GetSpecialFolderLocation = _
    Left$(strLocation, InStr(strLocation, vbNullChar) - 1)
    End If
    Call CoTaskMemFree(pidl)
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    Use like this:

    ActiveWorkbook.SaveAs GetSpecialFolderLocation(CSIDL_PERSONAL) & "Test.xls"

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving from a Macro (2003 SP1)

    If scripting is allowed, you can use the much shorter CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

    Dim strMyDocs As String
    strMyDocs = CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Saving from a Macro (2003 SP1)

    I'm a bit confused about the last post Hans.

    What do you mean by "if scripting is allowed". It looks to me like that code will access the Windows Scripting Host. Isn't that always avaliable within Excel? If it can be turned on and off, how do I toggle that?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving from a Macro (2003 SP1)

    In some corporate networks the network admin has disabled scripting through a system policy (scripting can be used to execute malicious code).

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Saving from a Macro (2003 SP1)

    OK. Scripting is turned on.

    So, I insert the suggested code into my macro that does the save:

    Dim strMyDocs As String
    strMyDocs = CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

    Now, in my macro I am saving a range from a worksheet to an HTML file like so:

    ThisWorkbook.Worksheets(1).Range("Details").Copy wbkTemp.Worksheets(1).[A1]
    wbkTemp.SaveAs Filename:="C:documents and settingssuudesktoppage1.htm", FileFormat:=xlHtml ' save Test2 as HTML

    Should I just change the path and filename to strMyDocs? This seems to work, but I am not following all of this with certainty.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving from a Macro (2003 SP1)

    Like this:

    wbkTemp.SaveAs Filename:=strMyDocs & "page1.htm", FileFormat:=xlHtml ' save Test2 as HTML

    strMyDocs & "page1.htm" concatenates the user's My Documents path retrieved by the scripting code with the file name supplied by you.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Saving from a Macro (2003 SP1)

    Thanks!

    EOM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •