Solved

Convert what you see in a folder in Windows Explorer (Details View) into an Excel spreadsheet.

Posted on 2010-08-23
8
718 Views
Last Modified: 2012-05-10
Is it possible to convert what you see in a folder in Windows Explorer (Details View) into an Excel spreadsheet?

Hence, the Excel spreadsheet will have a list of

- Filenames under Name column,
- dates under Date modified column
- file type  under type column
- file size under size column

I'm not sure which Zone this question belongs to. I hope this is the correct one.

Thanks!
0
Comment
Question by:jeremyll
  • 5
  • 3
8 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 33498678
yes, i can post a vb script which accept the folder path as argument and export to excel list all the columns u mentioned.is this solution good for you?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33498710
check the screenshot
Untitled.jpg
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33498860
here's the script.there are few constants in the script that you might want to change:FOLDER_PATH -> the folder from which the files properties are being exported to the excel worksheet,OUTPUT_EXCEL_FILE -> the output excel file pathto run the script yu have 2 options:via command line:cscript script_path.vbsvia windows explorer:right click on the script file -> Open With -> Microsoft Windows Based Script Host
Const FOLDER_PATH = "c:\temp\files"
const OUTPUT_EXCEL_FILE = "c:\temp\output.xlsx"
const SHEET_HEADERS = "Name, Date Modified, Type, Size"
Const xlExcel7 = 51

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

col=1
row=2

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = Replace(Replace(FOLDER_PATH, ":\", "-"), "\","-")

for each header in Split(SHEET_HEADERS,",")
	objSheet.Cells(1, col).Value = header
	col=col+1
next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FOLDER_PATH)
For Each objFile in objFolder.Files
	objSheet.Cells(row, 1).Value = objFile.Name
	objSheet.Cells(row, 2).Value = objFile.DateLastModified
	objSheet.Cells(row, 3).Value = objFile.Type
	objSheet.Cells(row, 4).Value = objFile.Size & " KB"
	row=row+1
Next

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs OUTPUT_EXCEL_FILE, xlExcel7
objExcel.ActiveWorkbook.Close false

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "done."

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:jeremyll
ID: 33506856
I received an error message after I ran the script

via windows explorer:
right click on the script file -> Open With -> Microsoft Windows Based Script Host

Please see attached screenshot
windowsScriptError.gif
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 33508106
try now.
Const FOLDER_PATH = "c:\temp\files"
const OUTPUT_EXCEL_FILE = "c:\temp\output.xlsx"
const SHEET_HEADERS = "Name, Date Modified, Type, Size"
Const xlExcel7 = 51

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

col=1
row=2

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

for each header in Split(SHEET_HEADERS,",")
	objSheet.Cells(1, col).Value = header
	col=col+1
next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FOLDER_PATH)
For Each objFile in objFolder.Files
	objSheet.Cells(row, 1).Value = objFile.Name
	objSheet.Cells(row, 2).Value = objFile.DateLastModified
	objSheet.Cells(row, 3).Value = objFile.Type
	objSheet.Cells(row, 4).Value = objFile.Size & " KB"
	row=row+1
Next

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs OUTPUT_EXCEL_FILE, xlExcel7
objExcel.ActiveWorkbook.Close false

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "done."

Open in new window

0
 

Author Comment

by:jeremyll
ID: 33517801
Thanks Sedgewick, your help is much appreciated.

I tried the above, but I'm still getting errors.

I've attached the vbs file that i tried to run and screenshot of the error.
convert2Excel.vbs
scriptError.gif
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 500 total points
ID: 33518460
open the .vbs file in notepad.
save it as ANSI (keep the same file anem and extension) and try run it again.
0
 

Author Closing Comment

by:jeremyll
ID: 33518546
Incredible!

Thanks so much Sedgewick!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question