The code adds a list of machines from an excel document to an array. It then sees if those machines are listed as files in a directory folder. If the machine name matches a file in the folder, it is supposed to add the content of the text file to the created excel document. The code seems to be working fine, with the exception that it doesn't write the data to the excel document. Any help would be appreciated!
Option Explicit
'This section Adds file names from Excel to Array
Dim arrExcelValues()
Dim objExcel, objWorkbook, strItem, i, x
Set objExcel = CreateObject ("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\jm\Test.xls")
objExcel.Visible = True
i = 1
x = 0
Do Until objExcel.Cells(i, 1).Value = ""
ReDim Preserve arrExcelValues(x)
arrExcelValues (x) = objExcel.Cells(i, 1).Value
i = i + 1
x = x + 1
Loop
objExcel.Quit
'This section checks the array names against files and then adds them to an excel file if found
Dim objFile, strDirectory, objfLD, objFSO, strFolder, objTS, FIL, strFilename, arraypos, ExcelPos, strContents, objTextFile, strFileLocation, objSheet, strExcelPath
Const ForReading = 1
Const xlExcel7 = 39
strFolder = "C:\Users\jm\Machines"
strExcelPath = "C:\Users\jm\myfile.xls"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFLD = objFSO.GetFolder(strFolder)
ExcelPos = 1
strFilename = arrExcelValues(arraypos)
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)
objSheet.Name = "Machines"
For Each Fil IN objFLD.Files
For arraypos=0 to UBound(arrExcelValues)
strFilename = arrExcelValues(arraypos) & "-Corp1"
If Fil.name = strFilename Then
strFileLocation = strFolder & "\" & strFilename
Set objTextFile = objFSO.OpenTextFile (strFileLocation, ForReading)
Do Until objTextFile.AtEndofStream
strContents = objTextFile.ReadLine
Loop
objTextFile.Close
objSheet.Cells(1, ExcelPos).Value = strContents
ExcelPos = ExcelPos + 1
End If
Next
Next
For Each Fil IN objFLD.Files
For arraypos=0 to UBound(arrExcelValues)
strFilename = arrExcelValues(arraypos) & "-Corp2"
If Fil.name = strFilename Then
strFileLocation = strFolder & "\" & strFilename
Set objTextFile = objFSO.OpenTextFile (strFileLocation, ForReading)
Do Until objTextFile.AtEndofStream
strContents = objTextFile.ReadLine
Loop
objTextFile.Close
objSheet.Cells(1, ExcelPos).Value = strContents
ExcelPos = ExcelPos + 1
End If
Next
Next
objExcel.ActiveWorkbook.SaveAs strExcelPath, xlExcel7
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit