FL340628SC,1,94,2,94,3,95,4,93,5,95
MA341282SC,1,83,2,94,3,93
NJ331947SC,1,95,2,183,3,94,4,94,5,95
NY345922SC,,,,,
FL340445SC,1,91,2,92,3,93,4,95,5,92
NJ336078SC,1,95,2,91,3,93,4,90,5,95,6,94
IL300716SC,,,,,
MA368952SC,2,95
MA356874SC,3,95,4,98
I am able to create the Excel spread sheet and headers but am having trouble trying to figure out how to be able to take the values, ie 1,95 2,94 and have them under a column corresponding to the first value (Node). Since not all of the values will be 1, 2, 3, 4 etc. some lines will contain 2, 94,3,94.
I want the Excel spreadsheet to look like:
Site Number | Node 1| Node 2| Node3 | Node 4| Node 5 | Node 6
FL340445SC| 91 | 92| 93 | 95| 92 |
MA368952SC| | 95| | | |
MA356874SC| | | 95| 98| |
This is what I have so far.
Any suggestions would be appreciated.
Code: Select all
Const ForReading = 1
Dim arrFields, strName, strNewContent,strLine, arrLine
Dim args : Set args = WScript.Arguments
If wscript.Arguments.Count = 0 Then
WScript.Echo "Usage: PCIUpdatetoExcel.vbs FilePath, i.e. C:Tempfile.txt"
WScript.Quit
End If
Dim strFilePath : strFilePath = args.Item(0)
Dim strFileName : strFileName = strFilePath
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FileExists (strFileName)then
WScript.Echo strFileName & " does not exist!!!"
WScript.Quit
Else
Dim objExcel : Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Caption = "Memory - Status Report"
Set objWorkBook = objExcel.Workbooks.Add ()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Range("A1").Cells(1) = "Site Number"
objExcel.Range("B1").Cells(1) = "Node 1"
objExcel.Range("C1").Cells(1) = "Node 2"
objExcel.Range("D1").Cells(1) = "Node 3"
objExcel.Range("E1").Cells(1) = "Node 4"
objExcel.Range("F1").Cells(1) = "Node 5"
objExcel.Range("G1").Cells(1) = "Node 6"
Set objWorkSheet = objWorkBook.Worksheets("Sheet1")
objWorkSheet.Name = "PCIUpdate - Status Report"
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)
i = 1
Do Until objFile.AtEndOfStream
i = i + 1
strLine = objFile.ReadLine
arrLine = Split(strLine,",")
objExcel.Cells(i, 1).Value = arrLine(0)
If arrLine(2) = "" Then
objExcel.Cells(i, 2).Value = ""
Else
objExcel.Cells(i, 2).Value = arrLine(2)
End If
If arrLine(4) = "" Then
objExcel.Cells(i, 3).Value = ""
Else
objExcel.Cells(i, 3).Value = arrLine(4)
End If
If arrLine(6) = "" Then
objExcel.Cells(i, 4).Value = ""
Else
objExcel.Cells(i, 4).Value = arrLine(6)
End If
If arrLine(8) = "" Then
objExcel.Cells(i, 5).Value = ""
Else
objExcel.Cells(i, 5).Value = arrLine(8)
End If
If arrLine(10) = "" Then
objExcel.Cells(i, 6).Value = ""
Else
objExcel.Cells(i, 6).Value = arrLine(10)
End If
If IsNull (arrLine(12)) Then
objExcel.Cells(i, 7).Value = ""
Else
objExcel.Cells(i, 7).Value = arrLine(12)
End If
Loop
'objExcel.Cells(1,1).EntireRow.Font.Bold = True
Set objRange3 = objWorkSheet.UsedRange 'This works even though the method does not show up
objRange3.AutoFilter
objRange3.EntireColumn.AutoFit
End If