Export to Excel On Click of a button

[Rev12]

Dear All,

I have listbox name lstfilename with a code below in forms class module

I have set the property to Multi Select,

This list box will display all my files in my TESTFILE folder

I need

1. that On Click from button cmdexcel it will export ALL the file names to excel if no selection
2. export to excel only if there were selected filenames

Please any help, thanks in advance. Rev12

Private Function ListFiles()


Dim sPath As String
Dim sFile As String
Dim sExtension As String


Me.lstfilename.RowSource = ""


If Me.chkdisplayalltype Then
sExtension = "*.*"
Else
sExtension = "*.pdf"
End If


sPath = "C:\TESTFILE"


sFile = Dir(sPath & sExtension)


Do While sFile <> vbNullString
If sFile <> "." And sFile <> ".." Then
Me.lstfilename.AddItem sFile
End If
sFile = Dir
Loop


End Function

[alansidman]

Maybe something like this will be helpful when run from Excel

Sub ListAllFile()


    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long


    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add


    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"


    'Loop through the Files collection
    For Each objFile In objFolder.Files
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing


End Sub

[NTC]

tricky stuff; the prior post may be spot on...... I don't like multi select Listboxes as they are a hassle to work with under more simple designs.

If you modify your module to write your list to a table, rather than a listbox control. Then the Listbox control source of a table is a more standard design. Meaning that: IF isnull(Listbox) THEN .... export entire table to excel - this is simple enough.

Then the question is how to use a multi select box value as criteria in a query - you can Bing around for Q&A on this topic - the controls value is going to be non normal i.e.: this, that

Because I dislike multi select Listbox controls - I myself would probably use that table that I suggest hold the list, add a second yes/no field - - and then display this so the users can put a check box in that field.... then querying this table becomes straight forward....