[RESOLVED] [EXCEL] Excel doesn't stay invisible after FileDialog closes. Really nobody???

[RaytracerFFM]

Hi experts,

i believe this is a simple one, but i couldn't find anything on the subject elsewhere. Guess i must be doing something wrong

My problem:

Excel is set to Visible = False. When I call Application.FileDialog(msoFileDialogFolderPicker) and the user either selects something or cancels out, the dialog is closed and Excel automatically becomes visible again.

Is there any way to keep Excel invisible after leaving FileDialog? I tried setting Visible to False again, which works, but you still can see Excel open up for a split second.

Here a code snippet that illustrates the problem:

Public Sub Test()
    Dim vrtFileNamePath As Variant
    
    Application.Visible = False

    With Application.FileDialog(msoFileDialogFolderPicker)
    
        .InitialView = msoFileDialogViewList
        
        If .Show = -1 Then
            vrtFileNamePath = .SelectedItems(1)
        End If
        
    End With
    
   Application.Wait (Now + TimeValue("0:00:03"))
   
   Application.Visible = True
   
End Sub

Excel shouldn't become visible again before Application.Visible is set True for as much as i understand.

Any help would be greatly appreciated

[Siddharth Rout]

Use the API's to solve your problem...

The following code is used for getting the folder name

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

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
    
Private 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

Public Sub Test()
    Dim FolderName As String
    
    Application.Visible = False
    
    FolderName = GetFolderName("Select a folder")
    
    If FolderName = "" Then
        MsgBox "You didn't select a folder."
    Else
        MsgBox "You selected this folder: " & FolderName
    End If
    
    Application.Wait (Now + TimeValue("0:00:03"))
    
    Application.Visible = True
End Sub

Function GetFolderName(Msg As String) As String
    Dim bInfo As BROWSEINFO, path As String, r As Long
    Dim X As Long, pos As Integer
    
    bInfo.pidlRoot = 0&
    
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    bInfo.ulFlags = &H1
    X = SHBrowseForFolder(bInfo)
    
    path = Space$(512)
    
    r = SHGetPathFromIDList(ByVal X, ByVal path)
    
    If r Then
        pos = InStr(path, Chr$(0))
        GetFolderName = Left(path, pos - 1)
    Else
        GetFolderName = ""
    End If
End Function

[Siddharth Rout]

And the following to get the File Name

Private Declare Function SetCurrentDirectoryA Lib "kernel32" _
(ByVal lpPathName As String) As Long

Public Sub Test()
    Dim sWBToOpen As Variant
    
    Application.Visible = False
    
    sWBToOpen = GetOpenFilenameFrom("c:\")
    
    Application.Wait (Now + TimeValue("0:00:03"))

    Application.Visible = True
End Sub

Public Function GetOpenFilenameFrom(Optional strDefDir As String) As Variant
    Dim strCurDir As String, lngErr As Long

    strCurDir = CurDir

    If strDefDir = vbNullString Then
        strDefDir = CurDir
    Else
        If Len(Dir(strDefDir, vbDirectory)) = 0 Then
            strDefDir = strCurDir
        End If
    End If

    If Not Left(strDefDir, 2) = "\\" Then
        ChDrive Left(strDefDir, 1)
        ChDir (strDefDir)
    Else
        lngErr = SetCurrentDirectoryA(strDefDir)
        If lngErr = 0 Then _
            MsgBox "Sorry, I encountered an error accessing the network file path"
        ChDir (strDefDir)
    End If

    GetOpenFilenameFrom = Application.GetOpenFilename _
    ("Excel Files (*.xl*), *.xl*,All Files (*.*),*.*")

    If Not Left(strCurDir, 2) = "\\" Then
        ChDrive Left(strCurDir, 1)
        ChDir (strCurDir)
    Else
        lngErr = SetCurrentDirectoryA(strCurDir)
        If lngErr = 0 Then _
            MsgBox "Error resetting the network file path"
        ChDir (strCurDir)
    End If
End Function

[RaytracerFFM]

hey koolsid

THANK YOU for your quick reply and your time!

Your solution totally rocks

I've seen this elsewhere before (i really like that folder browser) in a more complex context, but since my knowledge about external library calls is rather weak, i refrained from going into it and decided to stick with the tools Excel offers.

But your code is nice and slim enough for me to work it through.

Needless to say: you saved my week!

Again, thanks a million...

cheers,
ray