Deleting Rows in CSV from VBScript

I'm using this VBS in automation to open an Excel file, save it as a CSV, and then open the CSV and delete rows that don't have today's date.  I can't get the row deletion part to work, cause I suck at VBS.  See the bottom of the code where I tagged it.
option explicit
 
dim a, arg, oArgs, ArgNum
a = 0
 
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
 
if ArgNum <> 1 then
   WSCript.echo "Syntax: cscript <Script> filename_root"
   WScript.quit(1)
end if
 
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
 
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
 oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
 
pos = instr(oldfilename, " ")
 
if pos > 0 then
 
 newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
 newname = replace(newname," ","_")
 filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
 
 dim oShell
 set oShell = Wscript.CreateObject("WScript.Shell")
 Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
 oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
 
else
 filename_root = oldfilename
end if
 
dim app
set app = createobject("Excel.Application")
   
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
 
const xlXMLSpreadsheet = 46
const xlCSV = 6
 
app.DisplayAlerts = false
dim sht
Dim sDate
Dim sMonth
Dim sDay
 
If Month(Now()) < 10 Then
sMonth = "0" & Month(Now())
Else
sMonth = Month(Now())
End If
 
If Day(Now()) < 10 Then
sDay = "0" & Day(Now())
Else
sDay = Day(Now())
End If
 
sDate = Year(Now()) & sMonth & sDay
 
for each sht in wb.worksheets
 sht.activate
 dim output_filename
 output_filename = filename_root & "_" & sDate & ".csv"
 wb.saveAs output_filename, xlCSV
next
app.DisplayAlerts = true
 
wb.close false
 
'### problematic code
dim csv
set csv = app.workbooks.open( filename_root & "_" & sDate & ".csv" )
 
Rng = csv.Count.Rows
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value < sDate Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next
Application.ScreenUpdating = True
 
dim csv_output_filename
csv_output_filename = filename_root & "_" & sDate & ".csv"
csv.saveAs output_filename, xlCSV   
csv.close false
'### somebody help
 
WScript.quit

Open in new window

LVL 1
judas2158Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark PavlakCommented:
can you post a sample csv so I can get it to work
0
judas2158Author Commented:
Here is the Excel file.  It will get as far as converting it to a CSV (so you'll have the generated CSV file when you run it).  After that is the problem.
SNdata.xls
0
Mark PavlakCommented:
And you want all rows that do not have the current date(tdoays date) in the coulmn DATEENT to be removed.  Correct.  
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Mark PavlakCommented:
Add these lines and call the sub Usinng the FileName (full Path) to call it.  ie Clean_UP_CSV ("c:\temp\sample.csv")  it will save the file in the same dir only as sample_revised.csv
Sub Clean_UP_CSV (sFilename)
Dim oFso : Set oFso =CreateObject ("Scripting.FileSystemObject")
Const ForReading = 1
Dim oInfile : Set oInfile = oFso.OpenTextFile (sFilename,ForReading)
Dim oOutfile : oOutfile = oFso.CreateTextFile (Replace(sFilename,".csv","_Revised.csv"))
Dim sTemp
Do Until oInfile.AtEndOfStream
	sTemp = oInfile.ReadLine
If InStr (sTemp,BuildWritableDate) <> 0 Then
	oOutfile.WriteLine sTemp
Loop
End Sub
Function BuildWritableDate ()
Dim NewDate
NewDate = DatePart ("yyyy",Date)
If Len (DatePart ("M",Date)) < 2 Then
	NewDate = NewDate&"0"&DatePart ("M",Date)
Else
	NewDate = NewDate&DatePart ("M",Date)
End If 
If Len(DatePart ("d",Date)) < 2 Then
	NewDate = NewDate &"0"&DatePart ("d",Date)
Else
	NewDate = NewDate&DatePart ("d",Date)
End If
BuildWritableDate = NewDate
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
judas2158Author Commented:
Got a Loop without Do, put an End If before Loop; got a property not supported on oOutfile, had to put Set infront of it.  Made minor adjustments.  Works perfectly.  Thank you...  :)
'sDate defined elsewhere
Clean_UP_CSV (filename_root & ".csv")
 
Sub Clean_UP_CSV (sFilename)
Dim oFso : Set oFso =CreateObject ("Scripting.FileSystemObject")
Const ForReading = 1
Dim oInfile : Set oInfile = oFso.OpenTextFile (sFilename,ForReading)
Dim oOutfile : Set oOutfile = oFso.CreateTextFile (Replace(sFilename,".csv","_" & sDate & ".csv"))
Dim sTemp
Do Until oInfile.AtEndOfStream
	sTemp = oInfile.ReadLine
If InStr (sTemp,BuildWritableDate) <> 0 Then
	oOutfile.WriteLine sTemp
End If
Loop
End Sub
Function BuildWritableDate ()
Dim NewDate
NewDate = DatePart ("yyyy",Date)
If Len (DatePart ("M",Date)) < 2 Then
	NewDate = NewDate&"0"&DatePart ("M",Date)
Else
	NewDate = NewDate&DatePart ("M",Date)
End If 
If Len(DatePart ("d",Date)) < 2 Then
	NewDate = NewDate &"0"&DatePart ("d",Date)
Else
	NewDate = NewDate&DatePart ("d",Date)
End If
BuildWritableDate = NewDate
End Function

Open in new window

0
Mark PavlakCommented:
Your welcome.  Glad it worked pretty much first time I posted it quickly as I was going home for the day
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.