Solved

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

Posted on 2010-08-23
8
714 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
Comment Utility
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
Comment Utility
check the screenshot
Untitled.jpg
0
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
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
 

Author Comment

by:jeremyll
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Incredible!

Thanks so much Sedgewick!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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…
Viewers will learn the different options available in the Backstage view in Excel 2013.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now