Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0125-preview· OpenAIAi2 WildChat-4.8M (AI2)

Dim fs As Object, folder As Object, file As Object Dim ws As Worksheet Dim i As Integer…

  1. 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 = Nothing
    

    FOR 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

  2. Assistant

    To adjust the script you provided to search a .xlsm file for exact names of .txt files (without the .txt extension) 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 .txt files in a folder and writes values to an Excel sheet. Modifying it requires:

    1. To read from an .xlsm file, you'll need to open and iterate through a specific worksheet, searching for the filenames.
    2. Once you find a match (filename without extension), you'll need to identify the row of the found filename.
    3. You will then write the numeric value after "Placed:" to column G of that same row.

    Assuming you're dealing with a single .xlsm file that contains the names of .txt files, 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 .xlsm file's relevant data is in "Sheet1".

    Before you start, ensure the .xlsm worksheet (e.g., "Sheet1") contains the filenames in a particular column (e.g., column A) without the .txt extension.

    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 Sub
    

    This script assumes the filenames listed in the .xlsm file 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.