Results 1 to 10 of 10

Thread: Directory Listing to Excel Spreadsheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    5

    Directory Listing to Excel Spreadsheet

    Hey guys,

    I have been looking around and have not been able to find a way of doing this.

    What I need to do get a list of files of a certain extension (eg *.txt) and output the results to a specific excel spreadsheet..

    For example - directory listing of all txt files in C:\Test to excel sheet c:\Forms\Test.xls.

    If anyone know how this can be done that would be great.

  2. #2
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Directory Listing to Excel Spreadsheet

    Could try something like:

    VB Code:
    1. Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i, j
    2. folderpath = "C:\Test"
    3. Set fso = CreateObject("Scripting.FileSystemObject")
    4. Set objFolder = fso.GetFolder(folderpath)
    5. Set objFileList = objFolder.Files
    6.  
    7. Set xl = CreateObject("Excel.application")
    8. if not fso.folderexists("C:\Forms") then
    9.     fso.CreateFolder("C:\Forms")
    10. end if
    11. if not fso.fileexists("c:\Forms\Test.xls") then
    12.     xl.application.workbooks.add
    13.     xl.application.save("C:\Forms\Test.xls")
    14.     xl.application.quit
    15. End if
    16. xl.Application.Workbooks.Open "C:\Forms\Test.xls"
    17. xl.Application.Visible = True
    18. set objSheet = xl.ActiveWorkbook.Worksheets(1)
    19.  
    20. i = 1
    21. j = 1
    22.  
    23. For Each File In objFileList
    24.     fullpath = folderpath & "\" & file.name
    25.     if fso.getextensionname(fullpath) = "txt" then
    26.         objSheet.Cells(i, j).value = file.name
    27.         i = i + 1
    28.     end if
    29. Next
    30.  
    31. xl.DisplayAlerts = FALSE
    32. objSheet.saveas("C:\Forms\Test.xls")
    33. xl.application.quit

    Edited just to say if you want to search for a different file extension, say gif, then change "txt" to "gif".
    Last edited by l12ngo; Apr 19th, 2006 at 04:01 AM.

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    5

    Re: Directory Listing to Excel Spreadsheet

    Thanks for that, that worked fine.

    Just a quick question is it possible to make append to the next column.

  4. #4
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Directory Listing to Excel Spreadsheet

    Yes, if you look at the line:

    objSheet.Cells(i, j).value = file.name


    j is a variable which is set to 1 in my code. i is another variable which is set to one, but is incremented by 1 each time we loop round. So really, what we're saying is

    objSheet.Cells(1, 1).value = file.name

    for the first iteration of the loop

    objSheet.Cells(2, 1).value = file.name

    for the second etc etc. Now all this is code is doing is setting the value of the cell to file.name where i is the row number of the cell and j is the column number. If I wanted to set the value of the second column then all i'd need to do is put in another line so my for loop could look something like:

    VB Code:
    1. For Each File In objFileList
    2.     fullpath = folderpath & "\" & file.name
    3.     if fso.getextensionname(fullpath) = "txt" then
    4.         objSheet.Cells(i, j).value = file.name
    5.         objSheet.Cells(i, 2).value = "Hello"
    6.         objSheet.Cells(i,j).Font.Bold = True ' not needed
    7.         objSheet.Columns("A:A").ColumnWidth = "20" 'not needed
    8.         i = i + 1
    9.     end if
    10. Next


    .. Hope this explains it. It's just about manipulating the numbers in your loop. You can also format cells and columns to meet your needs. I've thrown in two little examples in the For Loop which will set the first column width to 20 and will bold the cells in the first column. I've commented them as not needed so you can delete those lines if you want to, it's just there to show you how you can do other stuff to your spreadsheet.

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    5

    Re: Directory Listing to Excel Spreadsheet

    Again thanks for that it has been a real help.

    I added in that other section and ended up with the following bit below, I changed the I to 2 so that I can put a header line for the file descriptions. But is it possible to have the append to the next line or leave a line gap insert the date and then add on the directory listing. I know now I am getting more complex, but is it possible.

    Essentially what I am trying to create is a script that will update a list of files, but these will files will change on a daily basis so we need to know what files are missing and keep track of what files are being updated.

    Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i, j
    folderpath = "C:\Test"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fso.GetFolder(folderpath)
    Set objFileList = objFolder.Files

    Set xl = CreateObject("Excel.application")
    if not fso.folderexists("C:\Forms") then
    fso.CreateFolder("C:\Forms")
    end if
    if not fso.fileexists("c:\Forms\Test.xls") then
    xl.application.workbooks.add
    xl.application.save("C:\Forms\Test.xls")
    xl.application.quit
    End if
    xl.Application.Workbooks.Open "C:\Forms\Test.xls"
    xl.Application.Visible = True
    set objSheet = xl.ActiveWorkbook.Worksheets(1)

    i = 2
    j = 1

    For Each File In objFileList
    fullpath = folderpath & "\" & file.name
    if fso.getextensionname(fullpath) = "txt" then
    objSheet.Cells(i, j).value = file.name
    objSheet.Cells(i, 2).value = "Hello"
    objSheet.Cells(i,j).Font.Bold = True ' not needed
    objSheet.Columns("A:A").ColumnWidth = "20" 'not needed
    i = i + 1
    end if
    Next

    xl.DisplayAlerts = FALSE
    objSheet.saveas("C:\Forms\Test.xls")
    xl.application.quit
    Last edited by Navarre; Apr 20th, 2006 at 10:20 PM.

  6. #6
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Directory Listing to Excel Spreadsheet

    No worries about things getting more complicated, it's usual to keep adding complexity to scripts as your needs develop so we're good there.

    Now, there's good news and bad news. First the bad news, I'm not sure exactly what you're trying to do here and if it's what I'm thinking you're trying to do then we may need to modify our approach. At the moment, we're grabbing the names of all the files in C:\Test and then outputting them to an Excel spreadsheet. Each time we run our script though, it will overwrite Test.xls, so for comparisons sake, this may not be that useful. You mentioned adding a line-break and if you change the line where we increment i (i = i + 1) to i = i + 2 then you'll see what I mean. Now a line break will appear, but because we're just overwriting the cells, in this case the line breaks will have old data in them because all the VBScript does is to overwrite the cells that we tell it to set a new value to. So, if we're trying to compare the same file we're going to run into problems because the xls sheet is overwritten each time we run the VBScript. Hope this makes sense

    Now, the good news is that as well as having the ablity to get the name of the file, we can get the date it was last modified which looks like it's vital for your script, so we could run this:

    VB Code:
    1. Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i
    2. folderpath = "C:\Test"
    3. Set fso = CreateObject("Scripting.FileSystemObject")
    4. Set objFolder = fso.GetFolder(folderpath)
    5. Set objFileList = objFolder.Files
    6.  
    7. Set xl = CreateObject("Excel.application")
    8. if not fso.folderexists("c:\Forms") then
    9.     fso.CreateFolder("C:\Forms")
    10. end if
    11. if not fso.fileexists("c:\Forms\Test.xls") then
    12.     xl.application.workbooks.add
    13.     xl.application.save("c:\Forms\Test.xls")
    14.     xl.application.quit
    15. End if
    16. xl.Application.Workbooks.Open "c:\Forms\Test.xls"
    17. xl.Application.Visible = True
    18. set objSheet = xl.ActiveWorkbook.Worksheets(1)
    19.  
    20. i = 1
    21.  
    22. objSheet.Columns("A:A").ColumnWidth = "20"
    23. objSheet.Columns("B:B").ColumnWidth = "15"
    24. objSheet.Cells(i, 1).value = "File Name"
    25. objSheet.Cells(i, 1).Font.Bold = True
    26. objSheet.Cells(i, 2).value = "Date Modified"
    27. objSheet.Cells(i, 2).Font.Bold = True
    28.  
    29. i = 2
    30.  
    31. For Each File In objFileList
    32.     fullpath = folderpath & "\" & file.name
    33.     if fso.getextensionname(fullpath) = "txt" then
    34.         objSheet.Cells(i, 1).value = file.name
    35.         objSheet.Cells(i, 2).value = file.datelastmodified
    36.         i = i + 1
    37.     end if
    38. Next
    39.  
    40. xl.DisplayAlerts = FALSE
    41. objSheet.saveas("C:\Forms\Test.xls")
    42. xl.application.quit

    Now, like I mentioned earlier, if you need line breaks inserted between each file listing, you can change i = i + 1 to i = i + 2 or i = i + 3 etc. But, you need to make this decision from the beginning otherwise your spreadsheet will leave old data in the line breaks from the times you previously ran your script.

    What I guess I'm confused about is the way you need to compare the files. Are we always going to be dragging the data from C:\Test or will this directory change from day to day? Also, do we need to keep some sort of track on the files, like days they were modified etc? Also, when you say you need to know if a file is missing, what exactly does this mean? Because at the moment all the script is going to do is dump the info about the files in C:\Test to the spreadsheet and overwrite any info in there.

    Sorry if I'm missing the obvious, it's often difficult to understand what other people mean on a forum and likewise, if I haven't explained myself properly on some of these points then just let me know and I'll try to clarify

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    5

    Re: Directory Listing to Excel Spreadsheet

    Again thanks for the help.

    What my goal is to get a list of files that have been uploaded to a server by our stores, these files represent daily sales information that has been successfully copied across to the server.

    We also need to keep a log of all files that have been transferred to us in case we have to go back and find missing data.

    Essentially I want to keep adding the same information to the file, because once we have the list of files we can know what stores did not transfer their data (for whatever reason and manually got and get it). But also we can keep our history of all files for say a month. Then just rename the file and move it somewhere else so that they can keep monthly records or what ever.

  8. #8
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Directory Listing to Excel Spreadsheet

    Okay, try this and let me know what we need to do from here. I've commented this script so you know what's going on:

    VB Code:
    1. Option Explicit 'thrown this in to keep track of my variables
    2.  
    3. ' declare my variables
    4. Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i
    5. Dim TheDate, LastRow, objFileList, objSheet, File
    6. ' Path to where my files are
    7. folderpath = "C:\Test"
    8. ' set up some objects
    9. Set fso = CreateObject("Scripting.FileSystemObject")
    10. Set objFolder = fso.GetFolder(folderpath)
    11. Set objFileList = objFolder.Files
    12. Set xl = CreateObject("Excel.application")
    13. ' if my destination folder doesn't need creating, i create it
    14. if not fso.folderexists("c:\Forms") then
    15.     fso.CreateFolder("C:\Forms")
    16. end if
    17. ' create xl file with columns if it's not there
    18. if not fso.fileexists("c:\Forms\Test.xls") then
    19.     xl.application.workbooks.add
    20.     xl.application.save("c:\Forms\Test.xls")
    21.     set objSheet = xl.ActiveWorkbook.Worksheets(1)
    22.     i = 1
    23.     objSheet.Columns("A:A").ColumnWidth = "20"
    24.     objSheet.Columns("B:B").ColumnWidth = "15"
    25.     objSheet.Cells(i, 1).value = "File Name"
    26.     objSheet.Cells(i, 1).Font.Bold = True
    27.     objSheet.Cells(i, 2).value = "Date Modified"
    28.     objSheet.Cells(i, 2).Font.Bold = True
    29.     xl.DisplayAlerts = FALSE
    30.     objSheet.saveas("C:\Forms\Test.xls")
    31.     xl.application.quit
    32.  
    33. End if
    34. ' open up the xl sheet
    35. xl.Application.Workbooks.Open "c:\Forms\Test.xls"
    36. xl.Application.Visible = True
    37. set objSheet = xl.ActiveWorkbook.Worksheets(1)
    38. ' get todays date and place in a variable
    39. TheDate = Date()
    40. ' get the last row in the spreadsheet
    41. LastRow = objSheet.UsedRange.Rows.Count
    42. ' just an if statement in case it's the first entry
    43. ' i leave a line between updates by incrementing lastrow by 2
    44. ' if you want a bigger gap you could change this to 3, 4, 5 etc
    45. if LastRow = 1 then
    46.     i = 2
    47. else
    48.     i = LastRow + 2
    49. End if
    50. ' iterate through the files and write details to spreadsheet
    51. For Each File In objFileList
    52.     fullpath = folderpath & "\" & file.name
    53.     if fso.getextensionname(fullpath) = "txt" then
    54.         objSheet.Cells(i, 1).value = file.name
    55.         objSheet.Cells(i, 2).value = file.datelastmodified
    56.         i = i + 1
    57.     end if
    58. Next
    59. ' add a line to say when update to spreadsheet was ran
    60. objSheet.Cells(i,1).Font.ColorIndex = 11
    61. objSheet.Cells(i,1).Value = "Updated " & TheDate
    62.  
    63. ' save & close
    64. xl.DisplayAlerts = FALSE
    65. objSheet.saveas("C:\Forms\Test.xls")
    66. xl.application.quit

    This appends your file details rather than replacing them. If you need to add other comments, you can do that by adding in similar code to the bit where I write the line "Updated " & TheDate

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    5

    Re: Directory Listing to Excel Spreadsheet

    That worked just great, exactly what I wanted to do.

    Thanks again.

  10. #10
    New Member
    Join Date
    Aug 2009
    Posts
    1

    Re: Directory Listing to Excel Spreadsheet

    Great Script!! works pretty well. though i have made some adjustments, how do i get adjust the script to scan subfolders...
    looking forward to hearing from you.
    thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width