Saving from a Macro (2003 SP1)

Topic

New Reply

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?

Reply | Quote

Replies

Copy the following code at the top of a module:

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

Use like this:

ActiveWorkbook.SaveAs GetSpecialFolderLocation(CSIDL_PERSONAL) & “Test.xls”

Reply | Quote

If scripting is allowed, you can use the much shorter CreateObject(“WScript.Shell”).SpecialFolders(“MyDocuments”)

Dim strMyDocs As String
strMyDocs = CreateObject(“WScript.Shell”).SpecialFolders(“MyDocuments”)

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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(“MyDocuments”)

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.

Reply | Quote

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.

Reply | Quote

Thanks!

EOM

Reply | Quote