Loop through files

[Junior6202]

Hi all,

I found a script that I had modified a bit. The purpose of this script is to convert ".csv" files to Excel. The code works, but there are 600 csv files in the folder, I know that I have to set a loop to go through each file and convert it. I have the basic idea of what a loop does but I'm new to programming and not sure how to accomplish it. any help will be appreciated. Here is my code.


Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Const xlOpenXMLWorkbook = 51
MyDate = Replace(Date, "/", "-")
Set xl = CreateObject("Excel.Application")

xl.Workbooks.OpenText "P:\shipping\test\*.CSV", , , xlDelimited _
, xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
, Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
, Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook


wb.SaveAs "P:\shipping\test1\QVD_UPS" & MyDate & ".xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Quit

 

[markdmac]

Here is a simple example that just echoes out the file name of files from a folder. This should help you.

Set objFSO = Createobject("Scripting.FileSystemObject")
Set oFolder = objFSO.GetFolder("C:\temp")
For Each oFile in oFolder.Files
[indent]wscript.echo oFile.Name[/indent]
Next

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.