1

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
0

1 Answer 1

1

I figured it out!

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, colFiles, File
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)
Set colFiles = objFLD.files
ExcelPos = 1
strFilename = arrExcelValues(arraypos)
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)
objSheet.Name = "Machines"

For Each File IN colFiles
        For arraypos=0 to UBound(arrExcelValues) 
            strFilename = arrExcelValues(arraypos) & "-Domain1.txt"
            If File.name = strFilename Then
                strFileLocation = strFolder & "\" & strFilename
                Set objTextFile = objFSO.OpenTextFile (strFileLocation, ForReading)
                Do Until objTextFile.AtEndofStream
                    strContents = objTextFile.ReadLine
                Loop
                objTextFile.Close
                objSheet.Cells(ExcelPos, 1).Value = strContents

                ExcelPos = ExcelPos + 1
            End If
        Next
Next
For Each File IN colFiles
        For arraypos=0 to UBound(arrExcelValues) 
            strFilename = arrExcelValues(arraypos) & "-Domain2.txt"
            If File.name = strFilename Then
                strFileLocation = strFolder & "\" & strFilename
                Set objTextFile = objFSO.OpenTextFile (strFileLocation, ForReading)
                Do Until objTextFile.AtEndofStream
                    strContents = objTextFile.ReadLine
                Loop
                objTextFile.Close
                objSheet.Cells(ExcelPos, 1).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
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.