使用VB脚本，如何自动创建包含来自特定文件夹的文件列表的Excel文件?

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:

我所知道的是:(1)S:\集合中的一个文件列表的txt文件，可以在S:\Rally提示中创建。

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

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

(2)可以通过保存以下文件来打开一个新的Excel文件。

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):

然后在cmd中运行它(比如.vbs文件保存为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.

(1)是什么我不知道如何把这些放在一起,这样一个Excel文件(而不是创建一个txt文件)从年代的结果列表文件:\集会和(2)如何自动化,最好每个月运行结束时,我会没事的只是双击或者进入cmd并运行它自己每个月月底。

2 个解决方案

#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:

csv文件适合您的目的吗?要在S:\Rally中获取文件列表，可以在S:\Rally中保存一个批处理文件，其中包含一行代码:

dir /b > myCsv.csv

That would open in Excel.

在Excel中打开。

#2

(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

(1)如何将这些文件放在一起，以便使用S:\Rally生成的文件列表创建Excel文件(而不是txt文件)

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

这是一个脚本，它将标准输入复制到一个空白的Excel文件中，并将第一个参数作为文件名保存。

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.

运行它，将它保存为xldump。vbs并使用此命令行，将dir /b的输出导入脚本。

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.

(2)如何实现自动化，最好是在每个月底运行，但我可以在每个月底双击某样东西，或者进入cmd，自己运行它。

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

将之前的命令放在批处理文件中，并使用管理工具控制面板中的任务调度器来运行它。

#1