Home The below VBA code populates list of filenames in the same sheet, but I want the result dataset to be generated into a different worksheet?
Reply: 1

The below VBA code populates list of filenames in the same sheet, but I want the result dataset to be generated into a different worksheet?

sat
1#
sat Published in 2017-12-07 17:36:56Z

Please check the below query and help me in modifying the query, so that the result dataset gets populated into a different worksheet.

Sub MainList()
    'Updateby20150706
    Set folder = Application.FileDialog(msoFileDialogFolderPicker)
    If folder.Show <> -1 Then Exit Sub
    xDir = folder.SelectedItems(1)
    Call ListFilesInFolder(xDir, True)
    End Sub
    Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
    Dim xFileSystemObject As Object
    Dim xFolder As Object
    Dim xSubFolder As Object
    Dim xFile As Object
    Dim rowIndex As Long

    Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFileSystemObject.GetFolder(xFolderName)
    rowIndex = Application.ActiveSheet.Range("B65536").End(xlUp).Row + 1
    For Each xFile In xFolder.Files
      Application.ActiveSheet.Cells(rowIndex, 2).Formula = xFile.Name
      rowIndex = rowIndex + 1
    Next xFile
    If xIsSubfolders Then
      For Each xSubFolder In xFolder.Subfolders
        ListFilesInFolder xSubFolder.Path, True
      Next xSubFolder
    End If
    Set xFile = Nothing
    Set xFolder = Nothing
    Set xFileSystemObject = Nothing
    End Sub
    Function GetFileOwner(ByVal xPath As String, ByVal xName As String)
    Dim xFolder As Object
    Dim xFolderItem As Object
    Dim xShell As Object
    xName = StrConv(xName, vbUnicode)
    xPath = StrConv(xPath, vbUnicode)
    Set xShell = CreateObject("Shell.Application")
    Set xFolder = xShell.Namespace(StrConv(xPath, vbFromUnicode))
    If Not xFolder Is Nothing Then
      Set xFolderItem = xFolder.ParseName(StrConv(xName, vbFromUnicode))
    End If
    If Not xFolderItem Is Nothing Then
      GetFileOwner = xFolder.GetDetailsOf(xFolderItem, 8)
    Else
      GetFileOwner = ""
    End If
    Set xShell = Nothing
    Set xFolder = Nothing
    Set xFolderItem = Nothing
    End Function

Please let me know how to do the modification and let me know if you have any questions or comments. Thanks.

negas
2#
negas Reply to 2017-12-07 18:14:01Z

Add an index to Application.Worksheets(sheetIndex): Change

For Each xFile In xFolder.Files
      Application.ActiveSheet.Cells(rowIndex, 2).Formula = xFile.Name
      rowIndex = rowIndex + 1
    Next xFile

to

sheetIndex = 1
For Each xFile In xFolder.Files
      Application.Worksheets(sheetIndex).Cells(rowIndex, 2).Formula = xFile.Name
      rowIndex = rowIndex + 1
      sheetIndex = sheetIndex + 1
    Next xFile
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303118 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO