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

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 :confused:

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:

Code:

---

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 :)

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

The following code is used for getting the folder name

Code:

---

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

---

And the following to get the File Name

Code:

---

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

---

hey koolsid :)

THANK YOU for your quick reply and your time!

Your solution totally rocks :thumb:

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