Open the contents of Windows Explorer in to a form

[stacem]

I want to create a link to files that are contained within an NT folder. However, I don't want to display the standard Windows Explorer window panes to the user.

I wish to have the file names in the folder displayed in a form that I have created, how can this be done?

(I currently use a Hyperlink on a label control to take the user to the folder)

Any help appreciated.

 

[p27br]

you could populate a listbox with the file names.
you would need to create a module to populate the listbox.

 

[stacem]

p27br
I have heard/read about doing this before (somewhere) but if I recall it was not using standard VBA code and requires more coding depth than I have at present.

Do you have an example?

 

[idbr]

Hi Stacem,

The following is a routine I use to list files in Excel, which I've adapted slightly to import the results into an Access DB. I reckon it should be possible to modify it to run wholly in Access - any takers??

If you're using this to populate a listbox you'll want to run it from your form's 'onload' event. The listbox's data source should be tblFileList.

Warning - it's not overly fast...

Public Sub List_Files()

'-----------------------------------------------------------------
'Sub to list files in a folder
'Requires Microsoft Scripting Runtime Reference (Tools, References Menu)
'-----------------------------------------------------------------

'Declare any variables and constants

Dim fsoFolder As Folder
Dim fso As FileSystemObject
Dim intRowNumber As Integer
Dim strFileName As String '***Create your Excel File FIRST
Dim xlApp As Excel.Application

Set fso = New FileSystemObject

'Specify the path to the files
Set fsoFolder = fso.GetFolder("MyFolderPath") 'Full Folder Path Here

'Specify the Excel file being used to list them
strFileName = "MyExcelFilePath" 'You could set this based on the value in your form text box
                                'e.g. strFileName = Me!MyTextBox
                                '***Relies on you having the full path in there!

'Instantiate a new instance of Excel and then hide it
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    
    'Open the output file
    xlApp.Workbooks.Open strFileName
    
    'Set the counter
    intRowNumber = 1

'Loop through the files in the folder
For Each file In fsoFolder.Files
       
    'Activate the Excel sheet we're using to list the files
    xlApp.Worksheets(1).Activate
    
        'Goto the first data cell in the receiving sheet
        xlApp.Goto Reference:="R" & intRowNumber & "C1"
        
            With xlApp
    
              'Add in the filename
              .range("a" & intRowNumber).Select
              .range("a" & intRowNumber).Value = file.NAME
    
              'Save the book
              .ActiveWorkbook.Save
                    
            End With
    
    'Increase intRowNumber by 1 to reference the next (blank) row
    intRowNumber = intRowNumber + 1
    
    Debug.Print file
    
'Move to the next file
Next file

'Release Excel
xlApp.Quit

'Import the spreadsheet
DoCmd.TransferSpreadsheet acImport, , "tblFileList", strFileName, False

'Cleanup
Set fsoFolder = Nothing
Set fso = Nothing
Set xlApp = Nothing

End Sub

 

[idbr]

Forgot to mention - you need to create an Excel file to use to list the files in your folder. Do this first!

Iain

 

[PHV]

Why not simply use the Dir function to populate the ListBox ?

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

 

[stacem]

Thanks IDBR and PHV for your replies, sorry about the delay in responding.

I will investigate both but PHV suggestion regarding using the DIR function does looks more encouraging when looking this up within the A97 Help pages.

Stacem