Please help with listbox 2

[SkyHigh]

Hi

I am populating names of files in a folder in a listbox,
what I need to do is to open the file shown in the listbox column by double-clicking it, the following code doesn't work.

Thanks for your help
Brenda

---Code---

Private Sub lstFile2_DblClick(Cancel As Integer)
On Error GoTo Err_lstFile2_DblClick

Dim stAppName As String

stAppName = CurrentProject.path & "\Old MDB Files\" & lstFile2.ItemData


MsgBox stAppName
Call Shell(stAppName, 1)

Err_lstFile2_DblClick:
MsgBox Err.description
Exit Sub
End Sub

---Code---

 

[PHV]

The Shell VBA function works only with executables.
You may consider this alternate method:
Set sh = CreateObject("WScript.Shell")
sh.Run stAppName, 1 ', bWait

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkyHigh]

Thanks PHV but its now giving me error "Method 'Run' of object 'IWshShell3' failed"

BTW. I am listing .mdb files in the listbox and want to open the one being double-clicked.
Brenda

 

[PHV]

You may try this:
sh.Run Chr(34) & stAppName & Chr(34), 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkyHigh]

Great ! that worked, another question how do I open the file which is double clicked, like in the following line

stAppName = CurrentProject.path & "\Old MDB Files\" & lstFile2.ItemData

I have hardcoded ItemData to ItemData(0) for testing purpose so how I can change the code to open the one which is double-clicked.
Thanks again
Brenda

 

[PHV]

Have you tried this ?
stAppName = CurrentProject.Path & "\Old MDB Files\" & lstFile2.ItemData(ListIndex)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkyHigh]

Hi PHV
Even using the (ListIndex) no matter whatever file I double click on it opens up the very first file.

Any workaround to that.

Thanks much
Brenda

 

[PHV]

Is the ListBox MultiSelect ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkyHigh]

Nope, its set to "None" for "Multi Select"
Thanks
Brenda

 

[PHV]

Have you tried to move your code in the AfterUpdate event procedure of the ListBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkyHigh]

That didn't work either, here are all the functions that I am using to populate the listbox with files, please help me get it working, or is there another way of doing so - thanks

---Code---

Function ListFiles(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant

Static dbs(6000) As String
Static Entries As Integer
Dim ReturnVal As Variant
Dim strFolderName As String
Dim strTemp As String


ReturnVal = Null
Select Case code
Case acLBInitialize ' Initialize.
If Len(Nz(txtSelFolder)) = 0 Then
Entries = 0
Else
Entries = 0
'Build the directory name
If Right$(txtSelFolder, 1) = "\" Then
strFolderName = txtSelFolder & "*.*"
Else
strFolderName = txtSelFolder & "\" & "*.*"
End If
'
' If you use dynamic arrays
' find the total number of entries in the listbox
' Redim code to define dimensions of the array
' Before adding elements to the array
'
strTemp = Dir(strFolderName)
While strTemp <> ""
Entries = Entries + 1
dbs(Entries) = strTemp
strTemp = Dir
Wend
End If

ReturnVal = Entries
Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for control.
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
ReturnVal = -1 ' -1 forces use of default width.
Case acLBGetValue ' Get data.
ReturnVal = dbs(row + 1)

Case acLBEnd ' End.
Erase dbs

End Select
ListFiles = ReturnVal
End Function



Private Function fnGetList(strType As String) As String

Dim objfs As Object, objFolder As Object
Dim objfiles As Object, objf1 As Object
Dim strfill As String

If Len(Nz(txtSelFolder)) = 0 Then
fnGetList = ""
Exit Function
End If

Set objfs = CreateObject("Scripting.FileSystemObject")
Set objFolder = objfs.GetFolder(txtSelFolder)
If strType = "Files" Then
Set objfiles = objFolder.Files
Else
Set objfiles = objFolder.Subfolders
End If

For Each objf1 In objfiles
strfill = strfill & objf1.name & ";"
Next
Set objf1 = Nothing
Set objfiles = Nothing
Set objFolder = Nothing
Set objfs = Nothing

fnGetList = strfill

End Function

Private Sub Form_Load()
Me.txtSelFolder.Value = CurrentProject.path & "\Reports"
Me.lstFile2.Requery
End Sub

---Code---

 

[SkyHigh]

Can someone please help me !!!
Thanks much.

 

[SkyHigh]

Please help me get going - thanks.

 

[PJStephenson]

try changing:

stAppName = CurrentProject.path & "\Old MDB Files\" & lstFile2.ItemData

to:

stAppName = CurrentProject.path & "\Old MDB Files\" & lstFile2.value

Peter

 

[SkyHigh]

Cool that works like a charm !! Great !!
Thanks a bunch !!
Brenda