Do you see any errors/mistakes in this code?

[Yaik]

Here is a snippet of code from a program I have running. It seems that very often the script freezes at this part so I would like some help on figuring out what is wrong with this part.

Thank You

'#####################################################
'THIS PART WILL ELIMINATE THE NEW AND REMOVIED STATUS AFTER 30 DAYS
line=0
objFileNameOldtemp = location & "filenametemp1.csv"
objFSO.CopyFile objFileNameOld , objFileNameOldtemp
Set txtFile = objfso.OpenTextFile(objFileNameOldtemp, ForReading)

Do Until txtFile.AtEndOfStream
line=line+1
    strLine = txtFile.Readline
    tempstrLine = strLine
    MyArray = Split(strLine, ",", -1, 1)
    strLine = MyArray(4)
    If strLine <> "Status" Then

	If strLine = "Installed" Then
		Set objExcel = CreateObject("Excel.Application") 
		objExcel.Visible = False
		objExcel.DisplayAlerts = False
		objExcel.Workbooks.Add
		Set objWorkbook = objExcel.Workbooks.Open (objFileNameOld)
		objExcel.Cells(line, 1).Value = GetCustomDate
		objExcel.ActiveWorkbook.SaveAs objFileNameOld
		objExcel.ActiveWorkbook.Close
		objExcel.Application.Quit
	End If
	
	If DateDiff("d", MyArray(0), GetCustomDate) > 30 Then
		If strLine = "REMOVED" Then
		Set objExcel = CreateObject("Excel.Application") 
		objExcel.Visible = False
		objExcel.DisplayAlerts = False
		objExcel.Workbooks.Add
		Set objWorkbook = objExcel.Workbooks.Open (objFileNameOld)
		objExcel.Cells(line, 5).Value = "TOBEELIMINATED"
		objExcel.ActiveWorkbook.SaveAs objFileNameOld
		objExcel.ActiveWorkbook.Close
		objExcel.Application.Quit
		Else
		Set objExcel = CreateObject("Excel.Application") 
		objExcel.Visible = False
		objExcel.DisplayAlerts = False
		objExcel.Workbooks.Add
		Set objWorkbook = objExcel.Workbooks.Open (objFileNameOld)
		objExcel.Cells(line, 1).Value = GetCustomDate
		objExcel.Cells(line, 5).Value = "Installed"
		objExcel.ActiveWorkbook.SaveAs objFileNameOld
		objExcel.ActiveWorkbook.Close
		objExcel.Application.Quit
		End If	
        End If
    End If
Loop

If (objfso.FileExists(objFileNameOldtemp)) Then
objFSO.DeleteFile(objFileNameOldtemp)
End If

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
		Set objFile = objFSO.OpenTextFile(objFileNameOld, ForReading)

		Do Until objFile.AtEndOfStream
		    strLine = objFile.ReadLine
		    If InStr(strLine, "TOBEELIMINATED") = 0 Then
		        strNewContents = strNewContents & strLine & vbCrLf
		    End If
		Loop

		objFile.Close

		Set objFile = objFSO.OpenTextFile(objFileNameOld, ForWriting)
		objFile.Write strNewContents

		objFile.Close

		strNewContents = NULL

 

[jges]

When you say it 'freezes', does this mean it errors out; or simply seems to be unresponsive? Does it ever come out of 'freeze' mode or do you kill it with the task manager?

You are opening and closing Excel potentially twice for each line of your file you are reading, so I can see where it would slow down. Perhaps you can create the excel object before the loop and keep it alive until the end of the loop. Open and close excel files as needed within the loop. That should speed things up a bit.

 

[Yaik]

When it freezes I mean it hangs for ever and I have to kill it with task manager.

I use this script to loop around 200 computers. For the excel, I have about 3 excel files that need opening and closing depending on "if" statements and it happens all over again for each PC.

I'll see if by cleaning up the Excel opening and closing it speeds up or not.

Any other idea on why, or would you like me to post ALL of the code I use?