Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

This VBA code suddenly stopped working. It opens the file Dialog, but when I open the…

  1. User

    This VBA code suddenly stopped working. It opens the file Dialog, but when I open the folder location it shows it as empty. If I open Flie Explorer manually and open the same folder location all the files are shown

    Sub FileList() 'USED BY ALL CONTRACTOR FILES - DO NOT DELETE ' Unlock Sheet ActiveSheet.Unprotect Password:="edit" Application.EnableEvents = False Range("A5:A100").ClearContents Application.EnableEvents = True 'Open folder to copy files Dim xFSO As Object Dim xFolder As Object Dim xFile As Object Dim xFiDialog As FileDialog Dim xPath As String Dim i As Integer Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker) Dim filePath As String filePath = ThisWorkbook.path xFiDialog.InitialFileName = filePath If xFiDialog.Show = -1 Then xPath = xFiDialog.SelectedItems(1) End If Set xFiDialog = Nothing If xPath = "" Then Exit Sub Set xFSO = CreateObject("Scripting.FileSystemObject") Set xFolder = xFSO.GetFolder(xPath) i = 5 ' Modified line to start the list from A4 without row spaces between the links. For Each xFile In xFolder.files ActiveSheet.Hyperlinks.Add cells(i, 1), xFile.path, , , xFile.Name i = i + 1 ' Increment I by 1 for each file. Next ' Lock Sheet ActiveSheet.Protect Password:="edit" End Sub

  2. Assistant

    The issue you're encountering where the FileDialog opens, but the selected folder appears empty could potentially stem from several factors. One common cause may be related to how the files are being accessed or displayed in the FileDialog. Here are a few troubleshooting steps and suggestions that may help you resolve the issue:

    Possible Solutions

    1. Check Folder Permissions: Ensure that you have the necessary permissions to access the folder you're trying to open. File permissions or environmental settings might be causing the contents to not be displayed in the dialog.

    2. Use Full Path: Ensure that ThisWorkbook.Path returns a valid path. You can debug this by displaying the path in a message box or debug window. If ThisWorkbook.Path is empty or points to a non-existent directory, the dialog might not display properly.

      MsgBox ThisWorkbook.Path
      
    3. Change InitialFileName: If you're getting a folder path directly from ThisWorkbook.Path, try setting a specific known path to see if that resolves the issue. Alternatively, you can just omit the InitialFileName line to see if that makes a difference.

      ' xFiDialog.InitialFileName = filePath ' Comment this out for testing
      
    4. Check for Hidden Files: The folder you are attempting to access could contain hidden files. Ensure that you enable the option to view hidden files in the Explorer. In the VBA dialog, the visibility of files might differ based on system settings.

    5. Restart Excel: Sometimes, restarting Excel can resolve issues related to the FileDialog. A fresh session may restore the correct functionality.

    6. Check 64-bit vs. 32-bit Issue: If you recently upgraded your Office installation, be aware that changes in architecture (32-bit vs 64-bit) can affect how COM objects are instantiated. Ensure your code is compiled with the correct settings for your version of Office.

    Code Adjustment

    Make sure that your line where you are adding hyperlinks is correctly formatted and that the cells are referenced properly. Here’s a minor adjustment to ensure that you are referencing the cells correctly:

    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=xFile.Path, TextToDisplay:=xFile.Name
    

    Note about Error Handling

    You might want to add error handling to your code so you can capture any unexpected behavior, for example:

    On Error GoTo ErrorHandler
    
    ' Your existing code...
    
    ExitSub:
        ActiveSheet.Protect Password:="edit"
        Exit Sub
        
    ErrorHandler:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume ExitSub
    

    Implementing these suggestions should help you diagnose and potentially fix the issue with the File Dialog showing an empty folder. If the problem persists even after trying these suggestions, consider verifying system-wide settings or configurations that might affect file dialog behavior in your Excel installation.