Using VB Script, how do I automate the creation of an Excel file containing a list of files from a specific folder?

Question

I have searched the web but cannot find a specific answer to the following.

What I do know: (1) a txt file of a list of files in S:\Rally can be created from cmd at the S:\Rally prompt:

dir/b>H:\Home\list1.txt

(2) A new Excel file can be opened by saving the following as .vbs:

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

objExcel.Workbooks.Add

Then run it in cmd (say the .vbs file is saved as C:\Scripts\openex1.vbs):

cscript C:\Scripts\openex1.vbs

What I don't know is (1) how to put these together so that an Excel file (instead of a txt file) is created with the resulting list of files from S:\Rally and (2) how to automate this, preferably to run at the end of each month, but I would be fine to just double click something or go into cmd and run it myself at the end of each month.

JBStovers · Accepted Answer · 2016-01-08 06:45:22Z

1

Would a csv file suit your purpose? To get a list of files in S:\Rally, you could save a batch file in S:\Rally with one line in it:

dir /b > myCsv.csv

That would open in Excel.

answered Jan 8, 2016 at 6:45

JBStovers

3183 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tmdean · Accepted Answer · 2016-01-08 07:39:35Z

0

(1) how to put these together so that an Excel file (instead of a txt file) is created with the resulting list of files from S:\Rally

This is a script that will copy standard input into a blank Excel file and save it using the first argument as a filename.

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Add
xldata = Split(WScript.StdIn.ReadAll, vbCrLf)
Set a1 = xl.Evaluate("Sheet1!A1")
For i = 0 To UBound(xldata)
    a1.Offset(i).Value = xldata(i)
Next
wb.SaveAs WScript.Arguments(0)
xl.Quit

So to run it, save it as xldump.vbs and use this command line, piping the output of the dir /b into the script.

dir /b S:\Rally | cscript xldump.vbs file-list.xlsx

(2) how to automate this, preferably to run at the end of each month, but I would be fine to just double click something or go into cmd and run it myself at the end of each month.

Put the previous command in a batch file and use the Task Scheduler in the Administrative Tools control panel to run it.

edited Jan 8, 2016 at 7:39

answered Jan 8, 2016 at 7:14

Tmdean

9,32945 silver badges51 bronze badges

5 Comments

random13 Over a year ago

Thanks. However, this gives the error: 4,1 Microsoft VBScript runtime error: Active X component can't create object

Tmdean Over a year ago

You can't run this script by double clicking on it because it uses WScript.StdIn. Use the dir /b .... command line from my post.

random13 Over a year ago

Sorry, I should have been more specific. I did use your command line in cmd. Excel does come up but it is completely blank. Then cmd gives the aforementioned error.

random13 Over a year ago

Damn, it works perfectly. I guess the problem was stemming from account permissions or something because when I switched from work computer where I have limited permissions to home computer where I have admin it then works. Uncertain about this because I do have write rights to the folders in question. Either way, thanks a bunch!

Tmdean Over a year ago

Hmm, you might be using 32-bit office on a 64-bit OS on your work computer. Try changing the cscript to C:\Windows\SysWow64\cscript to use the 32-bit VBScript.

Collectives™ on Stack Overflow

Using VB Script, how do I automate the creation of an Excel file containing a list of files from a specific folder?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related