How to continuously write to an open Excel workbook

[jdobson54]

Hi All,

I'm running Excel 2010 on Windows 7 and have the following code that runs in a loop, parses a text file, and then inserts that output into an open Excel workbook. This works great the first time it runs, but on all subsequent runs it can't update the workbook and I'm assuming it's because it's open. The functionality I'm looking for would basically be like having an open text file and an open workbook and just copying from the text file and pasting into the workbook over and over. Is there a way to continually update the open workbook like this?

Const ForReading = 1
Const ForWriting = 2
 
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "\s([A-Z]+)\s+([\d\.]+)\s(\S+)\s\S+\s(\S+)\s(\S+)\s\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+\S+"
objRegEx.IgnoreCase = True
objRegEx.Global = True

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\test1.txt", ForReading)
f = objFile.ReadAll
objFile.Close

Set matches = objRegEx.Execute(f)

Dim i

If matches.Count > 0 Then
    Set objExcel = GetObject(,"Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Book1.xlsx")
    Set objSheet = objWorkbook.Sheets(1)
    objExcel.Application.Visible = True
    

    i=1
    For Each match in matches
        objExcel.Cells(i,1).Value = match.SubMatches(0)
        objExcel.Cells(i,2).Value = match.SubMatches(3)
        objExcel.Cells(i,3).Value = match.SubMatches(4)
        i=i+1
        Next
End If

Set objFile = objFSO.OpenTextFile("C:\test1.txt", ForWriting)
objFile.Write ""
objFile.Close