Populate File Path From Text File To Listbox

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
i have a text file that contains a list of file paths, how possible is it to populate the listbox with the file paths in a reverse range and that the listbox will show only the file name while the path to the file may be hidden in another column, so that i can use workbooks.open listbox1.value or any command button code to open the files?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Can you show an example of your file with the filenames and paths?
 
Upvote 0
Can you show an example of your file with the filenames and paths?

C:\users\user\desktop\business id.xls
c:\temp\quotation.xlsx
d:\my doc\private\real data.xls
the bold words are the file names, while the remaining ones are the paths. so, only the bold ones should appear in the listbox.
 
Upvote 0
This is the code that reads a file and loads the names

Code:
Option Explicit
Dim FileNum As Integer
Dim ReadStr As String
Dim ErrMsg As String
Dim ComLngFilename As String
Dim ComShtFileName As String
Dim ComDir As String
Dim WorkDir As String
Dim FileListArr() As String
Dim DirListArr() As String
Dim CharNo As Integer
Dim FileCount As Integer


Sub GetCommonFile()


    '========================================================
    'Open data file and read
    '========================================================
    'Get next available file handle number.
    FileNum = FreeFile()
    WorkDir = "C:\WorkFolder\"
    ComLngFilename = "FilenameList.txt"
    FileCount = 0
    ReDim Preserve FileListArr(FileCount)
    ReDim Preserve DirListArr(FileCount)
    
    'Set error trap in case of fault with data file.
    On Error GoTo ErrorCheck
    ErrMsg = "An error has occured in opening common list file."
    'Open text file for input.
    Open WorkDir & ComLngFilename For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    'Close error trap.
    On Error GoTo 0
    On Error GoTo CloseFile
    'Loop until the end of file is reached.
    Application.StatusBar = "Importing file " & ComLngFilename & " . . . ."
    Do While Seek(FileNum) <= LOF(FileNum)
    
      'Store one line of text from file to variable.
      Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , ReadStr
      
        If ReadStr <> "" Then
          For CharNo = Len(ReadStr) To 1 Step -1
            If Mid(ReadStr, CharNo, 1) = "" Then
              ComLngFilename = Right(ReadStr, Len(ReadStr) - CharNo)
              ComShtFileName = Left(ComLngFilename, Len(ComLngFilename) - 4)
              ComDir = Left(ReadStr, Len(ReadStr) - Len(ComLngFilename))
              Exit For
            End If
          Next CharNo
        End If
        
'store files
    DirListArr(FileCount) = ComDir
    FileListArr(FileCount) = ComLngFilename
    FileCount = FileCount + 1
    ReDim Preserve DirListArr(FileCount)
    ReDim Preserve FileListArr(FileCount)
    Loop 'Loop processing till eof.
'load listbox
ActiveWorkbook.Sheets("Sheet1").ListBox1.List = Application.Transpose(FileListArr)


CloseFile:
    'Close the open text file.
    Close
    
Exit Sub


ErrorCheck:
MsgBox ErrMsg, vbCritical, ThisWorkbook.Name
Exit Sub


End Sub

The listbox can then be clicked to select a file. Would you still need a textbox to search or just the code for the Listbox?
 
Upvote 0
this did not load the file names, rather it replaced the name of the text file with all the lines in the text file
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top