This VBA code suddenly stopped working. It opens the file Dialog, but when I open the…
- 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
- Assistant
The issue you're encountering where the
FileDialogopens, 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
-
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.
-
Use Full Path: Ensure that
ThisWorkbook.Pathreturns a valid path. You can debug this by displaying the path in a message box or debug window. IfThisWorkbook.Pathis empty or points to a non-existent directory, the dialog might not display properly.MsgBox ThisWorkbook.Path -
Change
InitialFileName: If you're getting a folder path directly fromThisWorkbook.Path, try setting a specific known path to see if that resolves the issue. Alternatively, you can just omit theInitialFileNameline to see if that makes a difference.' xFiDialog.InitialFileName = filePath ' Comment this out for testing -
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.
-
Restart Excel: Sometimes, restarting Excel can resolve issues related to the FileDialog. A fresh session may restore the correct functionality.
-
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.NameNote 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 ExitSubImplementing 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.
-