We help IT Professionals succeed at work.

I get an error when run this vbs

Hi,

I get an error when run this vbs

---------------------------
Windows Script Host
---------------------------
Script:      C:\Log.vbs
Line:      46
Char:      5
Error:      Input past end of file
Code:      800A003E
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
Can i get a refesh new file created each time i run. overrite the old file.

Regards
sharath

Dim data, tokens, fso, folder, files, sFolder, user, machine
Dim i, lines, row, objExcel, objSheet, objFileSystem, objInputFile
Const xlExcel7 = 39
Const EXCEL_OUTPUT_FILE = "d:\output.xls"
Const UNC_SOURCE_FOLDER = "\\s01\ger\LOGS"

row = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    WScript.Echo "Excel application not found."
    WScript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "Machines"
objSheet.Cells(1, 2).Value = "Users"
objSheet.Cells(1, 3).Value = "Data"

 
  Set fso = CreateObject("Scripting.FileSystemObject")

  
  Set folder = fso.GetFolder(UNC_SOURCE_FOLDER)
  Set files = folder.files
  
  For Each folderIdx In files
    'WScript.Echo "file: " & folderIdx.Name
    tokens = Split(folderIdx.Name, "_")
    machine = tokens(0)
    user = Replace(tokens(1), ".txt", "")
    'WScript.Echo "machine: " & machine
    
    Set objFileSystem = CreateObject("Scripting.fileSystemObject")
    Set objInputFile = objFileSystem.OpenTextFile(folderIdx, 1)
        
    lines = Split(objInputFile.ReadAll, vbNewLine)
    For i = 0 To UBound(lines)
        Line = lines(i)
                
        If InStr(Line, "ERROR:  Unable to get current user's SID") > 0 Then
            data = "Unable to get current user's SID"
        Else
            If InStr(Line, "Registry for current profile list backed up to:") > 0 Then
                data = Line
                
                If i + 2 <= UBound(lines) And InStr(lines(i + 2), "Operation complete") > 0 Then
                    data = data & " Operation complete"
                    i = i + 2
                End If
            Else
                If InStr(Line, "Operation complete") > 0 Then
                    data = "Operation complete"
                End If
            End If
        End If
    Next
    
'    WScript.Echo "data: " & data
 '   WScript.Echo

    objSheet.Cells(row, 1).Value = machine
    objSheet.Cells(row, 2).Value = user
    objSheet.Cells(row, 3).Value = data
    row = row + 1
  Next
  
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs EXCEL_OUTPUT_FILE, xlExcel7
objExcel.ActiveWorkbook.Close False

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2015

Commented:
Surround lines 45 - 65 with:
If Not EOF(objInputFile) Then
  ' Lines 45 - 65 here
End If

Open in new window

Author

Commented:
I get this
Can ou give me the full code
---------------------------
Windows Script Host
---------------------------
Script:      C:\Log.vbs
Line:      45
Char:      1
Error:      Type mismatch: 'EOF'
Code:      800A000D
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
Most Valuable Expert 2011
Top Expert 2015

Commented:
My mistake. Instead of EOF, use: AtEndOfStream
If Not objInputFile.AtEndOfStream Then

Open in new window

Commented:
Hi Sharath
I have amended your code to basically loop until or unless there is no data in the variable Lines(i)
For i = 0 To UBound(lines)
 If Lines(i) = "" Then
     Exit For
 End If

See if this works for you.
Regards
Krystian

Dim data, tokens, fso, folder, files, sFolder, user, machine
Dim i, lines, row, objExcel, objSheet, objFileSystem, objInputFile
Const xlExcel7 = 39
Const EXCEL_OUTPUT_FILE = "d:\output.xls"
Const UNC_SOURCE_FOLDER = "\\s01\ger\LOGS"

row = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    WScript.Echo "Excel application not found."
    WScript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "Machines"
objSheet.Cells(1, 2).Value = "Users"
objSheet.Cells(1, 3).Value = "Data"

 
  Set fso = CreateObject("Scripting.FileSystemObject")

  
  Set folder = fso.GetFolder(UNC_SOURCE_FOLDER)
  Set files = folder.files
  
  For Each folderIdx In files
    'WScript.Echo "file: " & folderIdx.Name
    tokens = Split(folderIdx.Name, "_")
    machine = tokens(0)
    user = Replace(tokens(1), ".txt", "")
    'WScript.Echo "machine: " & machine
    
    Set objFileSystem = CreateObject("Scripting.fileSystemObject")
    Set objInputFile = objFileSystem.OpenTextFile(folderIdx, 1)
        
    lines = Split(objInputFile.ReadAll, vbNewLine)
    For i = 0 To UBound(lines)
        If Lines(i) = "" Then 
	    Exit For
	End If        
	
        Line = lines(i)
                
        If InStr(Line, "ERROR:  Unable to get current user's SID") > 0 Then
            data = "Unable to get current user's SID"
        Else
            If InStr(Line, "Registry for current profile list backed up to:") > 0 Then
                data = Line
                
                If i + 2 <= UBound(lines) And InStr(lines(i + 2), "Operation complete") > 0 Then
                    data = data & " Operation complete"
                    i = i + 2
                End If
            Else
                If InStr(Line, "Operation complete") > 0 Then
                    data = "Operation complete"
                End If
            End If
        End If
    Next
    
'    WScript.Echo "data: " & data
 '   WScript.Echo

    objSheet.Cells(row, 1).Value = machine
    objSheet.Cells(row, 2).Value = user
    objSheet.Cells(row, 3).Value = data
    row = row + 1
  Next
  
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs EXCEL_OUTPUT_FILE, xlExcel7
objExcel.ActiveWorkbook.Close False

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Open in new window

Author

Commented:
Krystian
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Log.vbs
Line:      45
Char:      5
Error:      Input past end of file
Code:      800A003E
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
Most Valuable Expert 2011
Top Expert 2015

Commented:
The problem seems to be that you are trying to read an empty file. This is why I had you add the check for EOF (AtEndofStream).

Author

Commented:
There can be 1 or more txt files that are empty. if empty skip to next and in xls leave the row blank
Most Valuable Expert 2011
Top Expert 2015

Commented:
So add the aforementioned test for "AtEndOfStream".

Commented:
Try this, i've not had a chance to test sorry.
Krystian

Dim data, tokens, fso, folder, files, sFolder, user, machine
Dim i, FileData, lines, row, objExcel, objSheet, objFileSystem, objInputFile
Const xlExcel7 = 39
Const EXCEL_OUTPUT_FILE = "d:\output.xls"
Const UNC_SOURCE_FOLDER = "\\s01\ger\LOGS"

row = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    WScript.Echo "Excel application not found."
    WScript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "Machines"
objSheet.Cells(1, 2).Value = "Users"
objSheet.Cells(1, 3).Value = "Data"

 
  Set fso = CreateObject("Scripting.FileSystemObject")

  
  Set folder = fso.GetFolder(UNC_SOURCE_FOLDER)
  Set files = folder.files
  
  For Each folderIdx In files
    'WScript.Echo "file: " & folderIdx.Name
    tokens = Split(folderIdx.Name, "_")
    machine = tokens(0)
    user = Replace(tokens(1), ".txt", "")
    'WScript.Echo "machine: " & machine
    
    Set objFileSystem = CreateObject("Scripting.fileSystemObject")
    Set objInputFile = objFileSystem.OpenTextFile(folderIdx, 1)
        
    FileData = objInputFile.ReadAll


    If Trim(FileData) <> "" Then

        lines = Split(FileData, vbNewLine)
    

    For i = 0 To UBound(lines)
        If Lines(i) = "" Then 
	    Exit For
	End If        
	
        Line = lines(i)
                
        If InStr(Line, "ERROR:  Unable to get current user's SID") > 0 Then
            data = "Unable to get current user's SID"
        Else
            If InStr(Line, "Registry for current profile list backed up to:") > 0 Then
                data = Line
                
                If i + 2 <= UBound(lines) And InStr(lines(i + 2), "Operation complete") > 0 Then
                    data = data & " Operation complete"
                    i = i + 2
                End If
            Else
                If InStr(Line, "Operation complete") > 0 Then
                    data = "Operation complete"
                End If
            End If
        End If
    Next

    Else
        data = "No data in file"
    End If

'    WScript.Echo "data: " & data
 '   WScript.Echo

    objSheet.Cells(row, 1).Value = machine
    objSheet.Cells(row, 2).Value = user
    objSheet.Cells(row, 3).Value = data
    row = row + 1
  Next
  
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs EXCEL_OUTPUT_FILE, xlExcel7
objExcel.ActiveWorkbook.Close False

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing 

Open in new window

Author

Commented:
Still same error if a txt file has no data
Most Valuable Expert 2011
Top Expert 2015

Commented:
*sheesh*
Dim data, tokens, fso, folder, files, sFolder, user, machine
Dim i, lines, row, objExcel, objSheet, objFileSystem, objInputFile
Const xlExcel7 = 39
Const EXCEL_OUTPUT_FILE = "d:\output.xls"
Const UNC_SOURCE_FOLDER = "\\s01\ger\LOGS"

row = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    WScript.Echo "Excel application not found."
    WScript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "Machines"
objSheet.Cells(1, 2).Value = "Users"
objSheet.Cells(1, 3).Value = "Data"

 
  Set fso = CreateObject("Scripting.FileSystemObject")

  
  Set folder = fso.GetFolder(UNC_SOURCE_FOLDER)
  Set files = folder.files
  
  For Each folderIdx In files
    'WScript.Echo "file: " & folderIdx.Name
    tokens = Split(folderIdx.Name, "_")
    machine = tokens(0)
    user = Replace(tokens(1), ".txt", "")
    'WScript.Echo "machine: " & machine
    
    Set objFileSystem = CreateObject("Scripting.fileSystemObject")
    Set objInputFile = objFileSystem.OpenTextFile(folderIdx, 1)
        
    If Not objInputFile.AtEndOfStream Then
        lines = Split(objInputFile.ReadAll, vbNewLine)
        For i = 0 To UBound(lines)
            Line = lines(i)

            If InStr(Line, "ERROR:  Unable to get current user's SID") > 0 Then
                data = "Unable to get current user's SID"
            Else
                If InStr(Line, "Registry for current profile list backed up to:") > 0 Then
                    data = Line

                    If i + 2 <= UBound(lines) And InStr(lines(i + 2), "Operation complete") > 0 Then
                        data = data & " Operation complete"
                        i = i + 2
                    End If
                Else
                    If InStr(Line, "Operation complete") > 0 Then
                        data = "Operation complete"
                    End If
                End If
            End If
        Next

    '    WScript.Echo "data: " & data
     '   WScript.Echo

        objSheet.Cells(row, 1).Value = machine
        objSheet.Cells(row, 2).Value = user
        objSheet.Cells(row, 3).Value = data
        row = row + 1
      Next
    End If
  
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs EXCEL_OUTPUT_FILE, xlExcel7
objExcel.ActiveWorkbook.Close False

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Open in new window

Author

Commented:
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Log.vbs
Line:      75
Char:      7
Error:      Unexpected 'Next'
Code:      800A041F
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
Most Valuable Expert 2011
Top Expert 2015
Commented:
The indentation screwed me up. Corrected:
Dim data, tokens, fso, folder, files, sFolder, user, machine
Dim i, lines, row, objExcel, objSheet, objFileSystem, objInputFile
Const xlExcel7 = 39
Const EXCEL_OUTPUT_FILE = "d:\output.xls"
Const UNC_SOURCE_FOLDER = "\\s01\ger\LOGS"

row = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    WScript.Echo "Excel application not found."
    WScript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "Machines"
objSheet.Cells(1, 2).Value = "Users"
objSheet.Cells(1, 3).Value = "Data"

 
  Set fso = CreateObject("Scripting.FileSystemObject")

  
  Set folder = fso.GetFolder(UNC_SOURCE_FOLDER)
  Set files = folder.files
  
  For Each folderIdx In files
    'WScript.Echo "file: " & folderIdx.Name
    tokens = Split(folderIdx.Name, "_")
    machine = tokens(0)
    user = Replace(tokens(1), ".txt", "")
    'WScript.Echo "machine: " & machine
    
    Set objFileSystem = CreateObject("Scripting.fileSystemObject")
    Set objInputFile = objFileSystem.OpenTextFile(folderIdx, 1)
        
    If Not objInputFile.AtEndOfStream Then
        lines = Split(objInputFile.ReadAll, vbNewLine)
        For i = 0 To UBound(lines)
            Line = lines(i)

            If InStr(Line, "ERROR:  Unable to get current user's SID") > 0 Then
                data = "Unable to get current user's SID"
            Else
                If InStr(Line, "Registry for current profile list backed up to:") > 0 Then
                    data = Line

                    If i + 2 <= UBound(lines) And InStr(lines(i + 2), "Operation complete") > 0 Then
                        data = data & " Operation complete"
                        i = i + 2
                    End If
                Else
                    If InStr(Line, "Operation complete") > 0 Then
                        data = "Operation complete"
                    End If
                End If
            End If
        Next
    End If
    
    '    WScript.Echo "data: " & data
     '   WScript.Echo

        objSheet.Cells(row, 1).Value = machine
        objSheet.Cells(row, 2).Value = user
        objSheet.Cells(row, 3).Value = data
        row = row + 1
      Next
  
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs EXCEL_OUTPUT_FILE, xlExcel7
objExcel.ActiveWorkbook.Close False

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Open in new window