Need Help VBA macro

Question

I have this macro but get compile error below. why - how to solve?

Option Explicit

'                        +--------------------------+             +----------+

'------------------------|Windows Function Type Defs|-------------| 08/11/05 |

'                        +--------------------------+             +----------+

Public Type BROWSEINFO

    hOwner As Long

    pidlRoot As Long

    pszDisplayName As String

    lpszTitle As String

    ulFlags As Long

    lpfn As Long

    lParam As Long

    iImage As Long

End Type

'                         +-------------------------+             +----------+

'-------------------------|     zGetDirectory()     |-------------| 07/25/05 |

'                         +-------------------------+             +----------+

'Calls: N/A

'Notes: This function will bring up a form to let the user select a directory

Public Function zGetDirectory(Optional Msg) As String

    Dim bInfo As BROWSEINFO

    Dim zPath As String

    Dim lRetVal2 As Long, lRetVal As Long, iEndOfStr As Integer

    bInfo.pidlRoot = 0  '*** Root folder = Desktop ***

'***   Title in the dialog ***

    If IsMissing(Msg) Then

        bInfo.lpszTitle = "Select a Drive/Directory."

    Else

        bInfo.lpszTitle = Msg

    End If

    bInfo.ulFlags = &H1  '*** Type of directory to return ***

    lRetVal = SHBrowseForFolder(bInfo)  '*** Display the dialog ***

    zPath = Space$(512)     '*** Parse the result ***

    lRetVal2 = SHGetPathFromIDList(ByVal lRetVal, ByVal zPath)

    If lRetVal2 Then

        iEndOfStr = InStr(zPath, Chr$(0))

        zGetDirectory = Left(zPath, iEndOfStr - 1)

    Else

        zGetDirectory = ""

    End If

End Function             'zGetDirectory(Optional Msg)

Sub MySaveFile()

   Dim zFileName  As String

   Dim zDirName   As String

   zFileName = InputBox("Please Enter the desired file name.", "User Entry Required")

   If zFileName = "" Then

     MsgBox "File NOT saved you did not supply a filename!", _

            vbOKOnly + vbCritical, "Error: Filename missing!"

   Else

     zDirName = zGetDirectory("Select the desired drive\path")

     If zDirName = "" Then Exit Sub   '*** User Cancelled ***

     ChangeFileOpenDirectory zDirName

     ActiveDocument.SaveAs2 FileName:=zFileName & ".docm", FileFormat:= _

        wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _

        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _

        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _

        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14

   End If

End Sub

I get this compile error - don't know how to proceed....help

SHBrowseForFolder - BROWSEINFO

Answer 1

I have this macro but get compile error below. why - how to solve?

You forgot to declare the external function SHBrowseForFolder, but that would not solve your problem when you have a 64-bit office.

You can call the same dialog in a more elegant way:

Sub Example_ShellGetFolder()

  Debug.Print ShellGetFolder(CSIDL_PERSONAL, "Select a folder", BIF_BrowseFolder)

End Sub

Function ShellGetFolder( _

    Optional RootPath As Variant = CSIDL_PERSONAL, _

    Optional Caption As String = "", _

    Optional Options As vbShellGetFolderFlags = BIF_DefaultOptions) As String

  'http://msdn.microsoft.com/en-us/library/windows/desktop/bb774065(v=vs.85).aspx

  'RootPath kann ein String oder CSIDL-Konstante sein

  Dim objShell As Object, objBrowse As Object

  On Error Resume Next

  Set objShell = CreateObject("Shell.Application")

  'Dialog starten und RootPath zurückgeben

  If IsNumeric(RootPath) Then

    'Anfangspfad als Konstante

    Set objBrowse = objShell.BrowseForFolder(&H0, Caption, Options, CLng(RootPath))

  Else

    'Anfangspfad als String

    Set objBrowse = objShell.BrowseForFolder(&H0, Caption, Options, RootPath & Chr(0))

  End If

  ShellGetFolder = objBrowse.Self.Path

End Function

But I recommend not to use it, the best way is to use Application.FileDialog, have a look into the help, there is an example.

Andreas.