I have list of file directories in Col A. I want to be able to double click on the cell and the file directory to be opened to sellect the file to be opened via Excel
I have the following code below , but get bad file or number
Kindly test & amend my code
See full code below
I have the following code below , but get bad file or number
Code:
If Len(FolderPath) > 0 And Dir(FolderPath, vbDirectory) <> "" Then
Kindly test & amend my code
See full code below
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim SelectedFolderPath As String
Dim FolderPath As String
' Get the path of the folder that was selected in Windows Explorer
SelectedFolderPath = GetSelectedFolderPath()
' Check if the double-clicked cell is in column A and if the path is valid
If Target.Column = 1 And Len(SelectedFolderPath) > 0 And Dir(SelectedFolderPath, vbDirectory) <> "" Then
' Get the full path of the selected folder
FolderPath = SelectedFolderPath & "\" & Target.Value
Debug.Print "FolderPath = " & FolderPath ' Add this line for debugging purposes
' Check if the folder exists
If Len(FolderPath) > 0 And Dir(FolderPath, vbDirectory) <> "" Then
' Open the folder in Windows Explorer
Shell "explorer.exe """ & FolderPath & """", vbNormalFocus
End If
End If
End Sub
Function GetSelectedFolderPath() As String
Dim objShell As Object
Dim objFolder As Object
Dim objFolderItem As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\")
Set objFolderItem = objFolder.Self
GetSelectedFolderPath = objFolderItem.Path
End Function