Set fso = CreateObject( "Scripting.FileSystemObject" )
Set textFile = fso.OpenTextFile( "C:\Test_Pattern.txt", 1 )
contents = textFile.ReadAll
textFile.Close
Set rgxp = New Regexp
rgxp.Pattern = "\d{3}\-\d{4}" ' i.e. 123-6786
rgxp.IgnoreCase = False
rgxp.Global = True
Set matches = rgxp.Execute( contents )
msgbox "Number of Lots: " & matches.Count
Dim xl,wb,ws
Set xl=CreateObject("Excel.Application")
Set wb=xl.Workbooks.Open("D:\Lots_Available.xls")
Set ws=wb.Worksheets("sheet1")
For i=0 to matches.Count-1
ws.cells(i+1,1)=matches.Item(i)
Next
wb.SaveAs "C:\Users\psha64\Desktop\Lots_Number\nov14-43mn0.xls"
wb.Close
Set xl=nothing
Set textFile = fso.OpenTextFile( "C:\Test_Pattern.txt", 1 )
contents = textFile.ReadAll
textFile.Close
Set rgxp = New Regexp
rgxp.Pattern = "\d{3}\-\d{4}" ' i.e. 123-6786
rgxp.IgnoreCase = False
rgxp.Global = True
Set matches = rgxp.Execute( contents )
msgbox "Number of Lots: " & matches.Count
Dim xl,wb,ws
Set xl=CreateObject("Excel.Application")
Set wb=xl.Workbooks.Open("D:\Lots_Available.xls")
Set ws=wb.Worksheets("sheet1")
For i=0 to matches.Count-1
ws.cells(i+1,1)=matches.Item(i)
Next
wb.SaveAs "C:\Users\psha64\Desktop\Lots_Number\nov14-43mn0.xls"
wb.Close
Set xl=nothing