Dim fs As Object, folder As Object, file As Object Dim ws As Worksheet Dim i As Integer…
- User
Dim fs As Object, folder As Object, file As Object Dim ws As Worksheet Dim i As Integer Dim textFile As Integer Dim fileContent As String Dim RegEx As Object Dim matches As Object
' Ensure references to the active worksheet and FileSystemObject Set ws = ThisWorkbook.Sheets("Sheet1") Set fs = CreateObject("Scripting.FileSystemObject") ' Using the folder where the workbook is saved Set folder = fs.GetFolder(ThisWorkbook.Path) ' Start writing from row 2 in column G i = 2 For Each file In folder.Files If LCase(fs.GetExtensionName(file.Name)) = "txt" Then textFile = FreeFile() Open file.Path For Input As textFile fileContent = "" fileContent = Input(LOF(textFile), textFile) Close textFile ' Debug output to help diagnose the issue Debug.Print "Content of " & file.Name & ": " & fileContent Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = True .Pattern = "Placed\s*:\s*(\d+)" End With Set matches = RegEx.Execute(fileContent) If matches.Count > 0 Then Dim numericValue As String numericValue = matches(0).SubMatches(0) ' Debug print the captured value for verification Debug.Print "Captured Numeric Value: " & numericValue ' Write captured numeric value into column G starting from row 2 ws.Cells(i, 7).Value = numericValue i = i + 1 Else ' If no match found, print a message Debug.Print "No match found in " & file.Name End If ' Clean up RegExp object after use Set RegEx = Nothing End If Next file ' Clean up FileSystemObject after use Set fs = NothingFOR THIS CODE I WANT TO SEARCH IN .XLSM FILE EXACT NAME OF .TXT FILE (WITHOUT EXTENSION .TXT ) AND TO PUT IN EXACT SAME ROW AS NAME WAS FOUND NUMERIC VALUE AFTER Placed: on column G
- Assistant
To adjust the script you provided to search a
.xlsmfile for exact names of.txtfiles (without the.txtextension) and to put the numeric value found after "Placed:" in the same row as the name, you will need to make several changes. The script currently iterates through.txtfiles in a folder and writes values to an Excel sheet. Modifying it requires:- To read from an
.xlsmfile, you'll need to open and iterate through a specific worksheet, searching for the filenames. - Once you find a match (filename without extension), you'll need to identify the row of the found filename.
- You will then write the numeric value after "Placed:" to column G of that same row.
Assuming you're dealing with a single
.xlsmfile that contains the names of.txtfiles, the script would change like this. Note: This script focuses on the concept of searching within an Excel (xlsm) file for specific names and assumes your.xlsmfile's relevant data is in "Sheet1".Before you start, ensure the
.xlsmworksheet (e.g., "Sheet1") contains the filenames in a particular column (e.g., column A) without the.txtextension.Sub UpdateXLSMWithTXTData() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") 'Adjust the name as needed Dim fs As Object Set fs = CreateObject("Scripting.FileSystemObject") Dim textFile As Integer Dim fileContent As String, fileNameWithoutExt As String Dim folder As Object, file As Object Set folder = fs.GetFolder(ThisWorkbook.Path) Dim RegEx As Object Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = True .Pattern = "Placed\s*:\s*(\d+)" End With Dim row As Range, firstRow As Long, lastRow As Long Dim matches As Object firstRow = 2 'Assuming data starts from row 2; adjust as necessary lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row 'Change '1' if the filenames are in a different column For Each file In folder.Files If LCase(fs.GetExtensionName(file.Name)) = "txt" Then fileNameWithoutExt = fs.GetBaseName(file.Name) ' Iterate through each row in Column A to find a match for fileNameWithoutExt For Each row In ws.Range("A" & firstRow & ":A" & lastRow) If Trim(LCase(row.Value)) = fileNameWithoutExt Then 'Open and read TXT file textFile = FreeFile() Open file.Path For Input As textFile fileContent = Input(LOF(textFile), textFile) Close textFile 'Use RegExp to find match Set matches = RegEx.Execute(fileContent) If matches.Count > 0 Then Dim numericValue As String numericValue = matches(0).SubMatches(0) ' Write captured numeric value into column G of the found row ws.Cells(row.row, 7).Value = numericValue Else ' Optional: Handle no match case Debug.Print "No match found in " & file.Name End If ' Clean up Set matches = Nothing Exit For 'Exit loop after matching to avoid unnecessary checks End If Next row End If Next file ' Cleanup Set fs = Nothing Set RegEx = Nothing End SubThis script assumes the filenames listed in the
.xlsmfile are unique. If there are multiple rows with the same filename, this script will update only the first occurrence. Adjust the logic as necessary for your specific requirements, such as handling multiples or differently structured data. - To read from an