+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
Scripts Thread, need a script to take a DIR list to XLSX in Coding and Web Development; i've posted here too: Macro to list Directories in various location - per worksheet but basically i would like a ...
  1. #1

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    need a script to take a DIR list to XLSX

    i've posted here too:

    Macro to list Directories in various location - per worksheet

    but basically i would like a script/macro that takes the directory listings of variou server shares and put them in one excel spreadsheet i can then search through - an index if you like.

    has anyone got anything, or know of a way?

    J

  2. #2

    Join Date
    Jun 2008
    Location
    Northants
    Posts
    86
    Thank Post
    16
    Thanked 17 Times in 17 Posts
    Rep Power
    15
    You can dir > filename.txt to get the directory to a file if that's any help, I'm sure you could use a script/macro in excel to auto import (possibly).

  3. Thanks to tekins from:

    Mr_J (8th July 2013)

  4. #3

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    i tried that, didnt get it working



    Dim fPath
    Dim fCSV


    Set xl = CreateObject("Excel.Application")
    xl.Visible = True

    Dim wbCSV
    Dim wbMST

    Set wbMST = xl.ActiveWorkbook
    fPath = "C:\csv\" 'path to CSV files, include the final \
    Application.ScreenUpdating = False 'speed up macro
    Application.DisplayAlerts = False 'no error messages, take default answers
    fCSV = Dir(fPath & "*.csv") 'start the CSV file listing

    Do While Len(fCSV) > 0
    Set wbCSV = Workbooks.Open(fPath & fCSV) 'open a CSV file
    'delete existing sheet in Mstr
    If Evaluate("ISREF('[" & wbMST.Name & "]" & ActiveSheet.Name & "'!A1)") Then
    wbMST.Sheets(ActiveSheet.Name).Delete
    End If
    ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count) 'move new sheet into Mstr

    fCSV = Dir 'ready next CSV
    Loop

    Application.ScreenUpdating = True
    Set wbCSV = Nothing

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Here you go, this will create a file called file_list.xlsx inside the folder you run the script on (drag and drop the folder on top of the script file to run it_:

    Code:
    Option Explicit
    
    Dim Arg 			'Script argument
    Dim FSO			'File System Object
    Dim oFol			'Folder object
    Dim oFil			'File object
    Dim oFils		'Files
    Dim myExcel		'Excel object
    Dim myWorkbook		'Workbook object
    Dim mySheet			'new worksheet
    Dim filePath	'Path to file (need for Excel to open the file)
    Dim x
    
    
    'Check arguments
    If WScript.Arguments.Length < 1 Then
    	WScript.Quit
    End If 
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Arg = WScript.Arguments(0)
    If FSO.FolderExists(Arg) = False Then
       Set FSO = Nothing
       WScript.Quit
    End If
    
    Set myExcel = CreateObject("Excel.Application")
    myExcel.Visible = True
    myExcel.DisplayAlerts = False
    
    'Get folder informaton
    Set oFol = FSO.GetFolder(Arg)
    
    Set myWorkbook = myExcel.Workbooks.Add()
    Set mySheet = myWorkbook.Sheets.Add()
    
    x=0
    Set oFils = oFol.Files
    For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
    	mySheet.Range("$A$1").Offset(x, 0).Value = oFil.Name
    	x = x + 1
    Next 
    
    mySheet.Columns("A:A").EntireColumn.AutoFit
    	
    myWorkbook.SaveAs oFol & "/file_list.xlsx"
    myWorkbook.Close False
    Set mySheet = Nothing
    Set myWorkbook = Nothing
    
    myExcel.DisplayAlerts = True
    myExcel.Application.Quit
    Set myExcel = Nothing
    Set oFils = Nothing
    Set oFol = Nothing

  6. Thanks to LosOjos from:

    Mr_J (8th July 2013)

  7. #5

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    thanks, still a couple of issues

    thanks LosOjos - very nice, but it only gets the last item in the folder - and its getting files, rather that just folders. (so the XLSX file just had one line, with an MSI file in A1, despite there being 50+ folders).

    also the output is saved in the remote location, not the local location of where i am running the script from (C:\)

    any pointers would be greatly appreciated!

    J

  8. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Quote Originally Posted by Mr_J View Post
    thanks LosOjos - very nice, but it only gets the last item in the folder - and its getting files, rather that just folders. (so the XLSX file just had one line, with an MSI file in A1, despite there being 50+ folders).

    also the output is saved in the remote location, not the local location of where i am running the script from (C:\)

    any pointers would be greatly appreciated!

    J
    The file location is an easy fix - just edit the argument after myWorkbook.SaveAs to set the location and filename. Not sure why it's only displaying the last item in a folder though - it works fine for me listing all files in a folder.

    It'll need a little modification to display the contents of subfolders too. Before I tweak it, what format would you want it in?

    My automatic response would be something like this:
    Code:
    file_at_root.txt
    file2_at_root.txt
    sub/file1.txt
    sub/file2.txt
    sub/sub2/file.txt

  9. #7

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thanks again!

    i dont want subfolders, just the directories that are in the one folder...

    \\server1\share1

    I'll then modify it for the other 30+ servers i have.

    its only listing FILES not DIRECTORIES - its the directories i need, sorry!

    tried changing the saveas and i get "SaveAs method of workbook class failed" - i've changed it to:
    myWorkbook.SaveAs oFol & "c:\file_list.xlsx"

  10. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    OK, this should do it for you. Run it from the CLI (did it like this so you can write a BAT to call it on any folders you want):

    Code:
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''  dirtoxlsx.vbs																		''
    ''  Usage: dirtoxlsx.vbs /Root:"X:\Some\Folder" /SaveAs:"X:\filename.xlsx" [/ShowFiles]	''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Option Explicit
    
    Dim Root 			'Script argument
    Dim FSO			'File System Object
    Dim oFol			'Folder object
    Dim oSubFol
    Dim oFil			'File object
    Dim oFils		'Files
    Dim myExcel		'Excel object
    Dim myWorkbook		'Workbook object
    Dim mySheet			'new worksheet
    Dim filePath	'Path to file (need for Excel to open the file)
    Dim lineCount
    Dim NamedArgs
    
    Set NamedArgs = WScript.Arguments.Named
    'Check arguments
    If NamedArgs.Exists("Root") = False Or NamedArgs.Exists("SaveAs") = False Then
    	WScript.Echo "Usage: dirtoxlsx.vbs /Root:""X:\Some\Folder"" /SaveAs:""X:\filename.xlsx"" [/ShowFiles]"
    	WScript.Quit
    End If 
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Root = NamedArgs.Item("Root")
    If FSO.FolderExists(Root) = False Then
       Set FSO = Nothing
       WScript.Echo "The folder " & Root & " does not exist"
       WScript.Quit
    End If
    
    Set myExcel = CreateObject("Excel.Application")
    myExcel.Visible = True
    myExcel.DisplayAlerts = False
    
    'Get folder informaton
    Set oFol = FSO.GetFolder(Root)
    
    Set myWorkbook = myExcel.Workbooks.Add()
    Set mySheet = myWorkbook.Sheets.Add()
    
    lineCount = 0
    mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    mySheet.Range("$A$1").Offset(lineCount, 1).Value = oFol
    lineCount = lineCount + 1
    Iterate oFol, mySheet, lineCount
    	
    myWorkbook.SaveAs NamedArgs.Item("SaveAs")
    myWorkbook.Close False
    Set mySheet = Nothing
    Set myWorkbook = Nothing
    
    myExcel.DisplayAlerts = True
    myExcel.Application.Quit
    Set myExcel = Nothing
    Set oFils = Nothing
    Set oFol = Nothing
    
    Sub Iterate(oFol, ByRef mySheet, ByRef lineCount)
    	if NamedArgs.Exists("ShowFiles") Then
    		Set oFils = oFol.Files
    		For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
    			mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FILE"
    			mySheet.Range("$A$1").Offset(lineCount, 1).Value = oFil
    			lineCount = lineCount + 1
    		Next 
    	End If
    	
    	For Each oSubFol in oFol.SubFolders
    		mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    		mySheet.Range("$A$1").Offset(lineCount, 1).Value = oSubFol
    		lineCount = lineCount + 1
    		Iterate oSubFol, mySheet, lineCount
    	Next
    End Sub
    Call the script with the following arguments:
    • /Root: - the path to the root folder to run the script on
    • /SaveAs: - the name of the file to save to (WARNING: IT WILL OVERWRITE AN EXISTING FILE)
    • /ShowFiles - Optional, add this option to display files in the list as well as folders

  11. Thanks to LosOjos from:

    Mr_J (11th July 2013)

  12. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Did that last script help?

  13. #10

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,756
    Thank Post
    3,265
    Thanked 1,052 Times in 973 Posts
    Rep Power
    365
    something like so :

    Recursion And The FileSystemObject

    Need to do the steps explained on this site : excel - How do I use FileSystemObject in VBA? - Stack Overflow


    1.) Within Excel you need to set a reference to the VB script run-time library.
    2.) The relevant file is usually located at \Windows\System32\scrrun.dll
    3.) To reference this file, load the Visual Basic Editor (ALT-F11) Select Tools -> References from the drop-down menu. A listbox of available references will be displayed Tick the check-box next to 'Microsoft Scripting Runtime'

    The full name and path of the scrrun.dll file will be displayed below the listbox Click on the OK button
    @Mr_J - as above curious how you are getting on with this

    Thanks
    Last edited by mac_shinobi; 10th July 2013 at 05:15 PM.

  14. #11

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    looking great, thanks for that!

    is there an easy way to specify multiple folders? OR call the same script for various folders in the BAT file, but append the XLSX file instead of creating a new one?

    also - i dont want subfolders, just the folders that are in the folder i specify. I tried commenting this out:


    'For Each oSubFol in oFol.SubFolders
    'mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    'mySheet.Range("$A$1").Offset(lineCount, 1).Value = oSubFol
    'lineCount = lineCount + 1
    'Iterate oSubFol, mySheet, lineCount
    'Next

    but then i only got one result - the folder i specify!
    Last edited by Mr_J; 11th July 2013 at 11:34 AM.

  15. #12

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Quote Originally Posted by Mr_J View Post
    looking great, thanks for that!

    is there an easy way to specify multiple folders? OR call the same script for various folders in the BAT file, but append the XLSX file instead of creating a new one?

    also - i dont want subfolders, just the folders that are in the folder i specify. I tried commenting this out:


    'For Each oSubFol in oFol.SubFolders
    'mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    'mySheet.Range("$A$1").Offset(lineCount, 1).Value = oSubFol
    'lineCount = lineCount + 1
    'Iterate oSubFol, mySheet, lineCount
    'Next

    but then i only got one result - the folder i specify!
    To only show the folders inside your root folder, just comment out the call to Iterate in that loop i.e.:

    Code:
    For Each oSubFol in oFol.SubFolders
    	mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    	mySheet.Range("$A$1").Offset(lineCount, 1).Value = oSubFol
    	lineCount = lineCount + 1
    	'Iterate oSubFol, mySheet, lineCount '<--- this line is commented out, so the script won't run on all subfolders
    Next
    To specify multiple folders, you'd need a BAT script with a separate call to the VBS script for each folder.

    As for appending the XLSX - give me 5 minutes to update the script

  16. Thanks to LosOjos from:

    Mr_J (11th July 2013)

  17. #13

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    can't thank you enough for this! its going to make a huge difference!

  18. #14

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    OK, here's new improved code for you. If the file you specify in SaveAs already exists, it'll append it (on a sheet called "DIRLIST"), otherwise it'll create a new file. I've also added a new argument, ShowSubFolders - add that argument to have the script drill down in to all subfolders, leave it out if you just want your root folder and the folders directly inside it

    Code:
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''  dirtoxlsx.vbs																						''
    ''  Usage: dirtoxlsx.vbs /Root:"X:\Some\Folder" /SaveAs:"X:\filename.xlsx" [/ShowFiles /ShowSubFolders]	''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Option Explicit
    
    Dim Root 			'Script argument
    Dim SaveAs
    Dim FSO			'File System Object
    Dim oFol			'Folder object
    Dim oSubFol
    Dim oFil			'File object
    Dim oFils		'Files
    Dim myExcel		'Excel object
    Dim myWorkbook		'Workbook object
    Dim mySheet			'new worksheet
    Dim sh
    Dim filePath	'Path to file (need for Excel to open the file)
    Dim lineCount
    Dim NamedArgs
    
    Set NamedArgs = WScript.Arguments.Named
    'Check arguments
    If NamedArgs.Exists("Root") = False Or NamedArgs.Exists("SaveAs") = False Then
    	WScript.Echo "Usage: dirtoxlsx.vbs /Root:""X:\Some\Folder"" /SaveAs:""X:\filename.xlsx"" [/ShowFiles /ShowSubFolders]"
    	WScript.Quit
    End If 
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Root = NamedArgs.Item("Root")
    If FSO.FolderExists(Root) = False Then
       Set FSO = Nothing
       WScript.Echo "The folder " & Root & " does not exist"
       WScript.Quit
    End If
    
    SaveAs = NamedArgs.Item("SaveAs")
    
    Set myExcel = CreateObject("Excel.Application")
    myExcel.Visible = True
    myExcel.DisplayAlerts = False
    
    'Get folder informaton
    Set oFol = FSO.GetFolder(Root)
    
    if FSO.FileExists(SaveAs) then
    	Set myWorkbook = myExcel.Workbooks.Open(SaveAs)
    	For Each sh In myWorkbook.Sheets
    		If sh.Name="DIRLIST" then Set mySheet = sh
    	next
    else
    	Set myWorkbook = myExcel.Workbooks.Add()
    end if
    
    If IsEmpty(mySheet) then
    	Set mySheet = myWorkbook.Sheets.Add()
    	mySheet.Name = "DIRLIST"
    	lineCount = 0
    else
    	lineCount = mySheet.UsedRange.Rows.Count
    end if
    
    mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    mySheet.Range("$A$1").Offset(lineCount, 1).Value = oFol
    lineCount = lineCount + 1
    Iterate oFol, mySheet, lineCount
    	
    myWorkbook.SaveAs NamedArgs.Item("SaveAs")
    myWorkbook.Close False
    Set mySheet = Nothing
    Set myWorkbook = Nothing
    
    myExcel.DisplayAlerts = True
    myExcel.Application.Quit
    Set myExcel = Nothing
    Set oFils = Nothing
    Set oFol = Nothing
    
    Sub Iterate(oFol, ByRef mySheet, ByRef lineCount)
    On Error Resume Next
    
    	if NamedArgs.Exists("ShowFiles") Then
    		Set oFils = oFol.Files
    		For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
    			mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FILE"
    			mySheet.Range("$A$1").Offset(lineCount, 1).Value = oFil
    			lineCount = lineCount + 1
    		Next 
    	End If
    	
    	For Each oSubFol in oFol.SubFolders
    		mySheet.Range("$A$1").Offset(lineCount, 0).Value = "FOLDER"
    		mySheet.Range("$A$1").Offset(lineCount, 1).Value = oSubFol
    		lineCount = lineCount + 1
    		If NamedArgs.Exists("ShowSubFolders") Then Iterate oSubFol, mySheet, lineCount
    	Next
    End Sub
    EDIT:
    one last piece of advice - if you're going to be calling this from a BAT then I suggest calling it like so (assuming you save the script as dirtoxlsx.vbs):

    Code:
    cscript dirtoxlsx.vbs /Root:\\server\Folder\Example /SaveAs:C:\example\dir.xlsx
    The reason being that if you don't call it with cscript, any errors will be windows dialogs causing the script (and in turn your BAT) to stall until the user presses OK. Obviously no good for an unattended run! Using cscript ensures all output is directed to the console.

    As an added bonus, if you were to run this as a scheduled task, you can redirect the output to a text file to create a log so you can see if anything went wrong e.g.

    Code:
    cscript dirtoxlsx.vbs /Root:\\server\Folder\Example /SaveAs:C:\example\dir.xlsx > C:\example\log.txt
    The result would be any output from the script would be stored in the log.txt file for you to check when you get chance
    Last edited by LosOjos; 11th July 2013 at 12:39 PM.

  19. Thanks to LosOjos from:

    Mr_J (12th July 2013)

  20. #15

    Join Date
    Jul 2011
    Location
    West Yorkshire
    Posts
    17
    Thank Post
    16
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    awesome, thanks so much!

    i'm sure this is going to be useful for a few people!

    J

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. terminal script to create home dirs in OS X
    By HodgeHi in forum Coding
    Replies: 6
    Last Post: 3rd July 2009, 10:13 AM
  2. need a script to copy a folder with shortcuts
    By jamin100 in forum Scripts
    Replies: 1
    Last Post: 4th September 2008, 07:38 PM
  3. Replies: 21
    Last Post: 29th December 2007, 10:53 PM
  4. need a script to delete shortcuts
    By philtomo-25 in forum Scripts
    Replies: 2
    Last Post: 1st November 2007, 03:50 PM
  5. Need a script to run programs
    By timbo343 in forum Scripts
    Replies: 9
    Last Post: 26th September 2007, 12:24 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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