I have a little VB knowledge but this one is way beyond my scope. This is a multipart problem I am trying to find a solution.
We have a tab delineated text file with 11 fields that is constantly updated by another program. We would like to copy/import just two fields of that data, let’s say fields #9 & #11 into Excel but only bring in the new data since the previous import. So if initially there are 5 lines of data, that would come in and populate the first 5 rows and 2 columns in a worksheet. Then subsequently it would bring in only anything added after the initial copy/import. I have been able to write a routine that simply copies everything and overwrites what is there but this is not what we want.
The next part of this is that based on certain criteria, the data will be copied/imported into certain columns. The criteria variables will be in fields #2 and #6. So for example, if field #2 = 1, and field #6 = 2, then fields #9 & #11 will go in columns A & B. If field #2 = 2 and field #6 = 1, then fields #9 & #11 will go into columns C&D. There would most likely be 6 possible combinations of those variables (1/1, ½, 1/3, 2/1, 2/2, 2/3) so columns A-L could be populated. Note that field #9 could be blank in which case it would just bring in field #11 or just copy the blank text. These variable numbers could be referenced to cells in the workbook where we select which variables will correspond to which columns. For example if we wanted columns A & B to contain the data with the variables 1 & 2, cells G1 & H1 could have #1 & #2 in their cells if that is helpful. There may be times when we want that variable combo of 2/1 to go into cells A & B.
Here is the text file:
0 1 2 0 0 1 b 1648 1648 9:55:02.61 09:55:03
0 2 5 0 0 1 b 1648 1659 9:57:45.70 09:57:46
0 2 7 0 0 2 b 1649 1649 9:58:09.84 09:58:10
0 3 8 0 0 1 s 9:58:09.87 09:58:10
0 1 9 0 0 1 b 1656 1656 9:58:13.80 09:58:15
0 1 11 0 0 1 b 1657 1657 9:58:19.88 09:58:20
0 3 12 0 0 1 s 9:58:19.93 09:58:20
0 1 13 0 0 1 b 1663 1663 9:58:25.77 09:58:26
0 1 14 0 0 2 s 9:58:25.78 09:58:26
0 1 15 0 0 2 s 9:59:22.40 09:59:23
0 2 16 0 0 1 b 1664 1664 9:59:22.41 09:59:23
0 2 17 0 0 2 s 1671 1671 9:59:26.33 09:59:27
This is the text file and what the worksheet should look like
The last part is ideally this routine would only run when the text file has been updated but I am not sure how possible that is. Otherwise I can just put this on a timer that will run every minute or so.
Being a bit of neophyte with VBA I have found this forum to be an awesome resource so I hope I have explained this correctly. If this info is already posted somewhere please point me in that direction. I am always willing to learn but this one is bit beyond me right now.
As always, many thanks in advance.
I have a little VB knowledge but this one is way beyond my scope. This is a multipart problem I am trying to find a solution.
This is code we use to import the entire text file
Public Sub ImportSRTfile()
Dim strSpec As String, i As Long, colToRet As Long, lastR As Long
Dim arrSp As Variant, arrRez() As String, arrInt As Variant, j As Long, K As Long
Dim FSO As Object, txtStr As Object, strText As String 'no need of any reference
Set shT = ThisWorkbook.Sheets("srt_data")
Set FSO = CreateObject("Scripting.FileSystemObject")
strSpec = "C:\srt_timing\srt_data_acquisition_module\srt_backup_timer_data.txt"
If Dir(strSpec) <> "" Then 'check if file exists
Set txtStr = FSO.OpenTextFile(strSpec)
strText = txtStr.ReadAll
txtStr.Close
End If
arrSp = Split(strText, vbCrLf)
colToRet = 11 'Number of columns to be returned
lastR = shT.Range("A" & Rows.Count).End(xlUp).Row 'last row in A:A
'arrRez is dimensioned from 0 to UBound(arrSp) only for lastR = 1
ReDim arrRez(IIf(lastR = 1, 0, 1) To UBound(arrSp), colToRet - 1)
For i = IIf(lastR = 1, 0, 1) To UBound(arrSp) 'Only in case of larR = 1, the
'head of the table is load in arr
arrInt = Split(arrSp(i), vbTab) 'each strText line is split in an array
If UBound(arrInt) > colToRet - 1 Then
For j = 0 To colToRet - 1
arrRez(i, j) = arrInt(j) 'each array element is loaded in the arrRez
Next j
End If
Next i
'The array is dropped in the dedicated range (calculated using Resize):
shT.Range("A" & IIf(lastR = 1, lastR, lastR 1)).Resize(UBound(arrRez, 1), _
UBound(arrRez, 2) 1).Value = arrRez
End Sub
CodePudding user response：
'This is code we use to import the entire text file
Public Sub ImportSRTfile()
Dim strSpec As String, i As Long, colToRet As Long, lastR As Long
Dim arrSp As Variant, arrRez() As String, arrInt As Variant, j As Long, K As Long
Dim FSO As Object, txtStr As Object, strText As String 'no need of any reference
Set shT = ThisWorkbook.Sheets("srt_data")
Set FSO = CreateObject("Scripting.FileSystemObject")
strSpec = "C:\srt_timing\srt_data_acquisition_module\srt_backup_timer_data.txt"
If Dir(strSpec) <> "" Then 'check if file exists
Set txtStr = FSO.OpenTextFile(strSpec)
strText = txtStr.ReadAll
txtStr.Close
End If
arrSp = Split(strText, vbCrLf)
colToRet = 11 'Number of columns to be returned
lastR = shT.Range("A" & Rows.Count).End(xlUp).Row 'last row in A:A
'arrRez is dimensioned from 0 to UBound(arrSp) only for lastR = 1
ReDim arrRez(IIf(lastR = 1, 0, 1) To UBound(arrSp), colToRet - 1)
For i = IIf(lastR = 1, 0, 1) To UBound(arrSp) 'Only in case of larR = 1, the
'head of the table is load in arr
arrInt = Split(arrSp(i), vbTab) 'each strText line is split in an array
If UBound(arrInt) > colToRet - 1 Then
For j = 0 To colToRet - 1
arrRez(i, j) = arrInt(j) 'each array element is loaded in the arrRez
Next j
End If
Next i
'The array is dropped in the dedicated range (calculated using Resize):
shT.Range("A" & IIf(lastR = 1, lastR, lastR 1)).Resize(UBound(arrRez, 1), _
UBound(arrRez, 2) 1).Value = arrRez
End Sub