concatenate loop to end of row

[mak0316]

Hello I am pretty new to VBA programing and I am stuck on a Concatenate problem.

I have a .txt document (outputted from a XML program) that I have to upload into excel and manually format to meet our reporting standards (outside reporting agency) anyway, I have set up a Sub to separate the data into useful chunks

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Evaluate("SUMPRODUCT(COUNTIF(" & .Cells(i, TEST_COLUMN).Address & _
",{""*COMPLETED*"",""*Expires*""}))") > 0 Then
Rows(i).Offset(1).EntireRow.Insert xlShiftDown
End If
Next i

End With
End Sub

But now I am trying to Concatenate the data from the 4 to 5 cell "chunks" to 1 cell that contains all the data. for example

Colum A colum A (on separete sheet)
Partial data 1 Complete data 1, 2, 3, & 4
Partial data 2 Complete data 1, 2, 3, & 4
Partial data 3
Partial data 4
Blank row
Partial data 1 ect

I am trying to make a looping Concatenate function but I cannot get it to work. I correctly concatenates the first set of data but then stops at the blank row. Is there a way to have it continue to loop?

Here is what I have so far

Public Function ColConc(CellRef As Range, Delimiter As String)

Dim LoopVar As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Concat As String
Dim Col As Long
With Cells.SpecialCells(xlCellTypeConstants)
Col = CellRef.Column
StartRow = CellRef.Row
EndRow = CellRef.End(xlDown).Row
Concat = ""
For LoopVar = StartRow To EndRow
Concat = Concat & Cells(LoopVar, Col).Value
If LoopVar <> EndRow Then Concat = Concat & Delimiter
LoopVar
End With
ColConc = Concat
End Function

I thought the LoopVAr would have the sub continue on to the next set but it does not so I am lost.
thanks for any help in advance

 

[mak0316]

Thank you so much for you help however I get the following error when I try to run the code

"Run-time error '52':

Bad file name or number"

I created a new module in my tracking.xlsm copy and pasted the file location for the test.txt file but i get that runtime error

the Debuger highlights the "Open strFile For Binary As #FF

 

[AlphaFrog]

Show what you did to this line.

strFile = ThisWorkbook.Path & "\Test.Txt" 'file path and name

Double check your path and file name.

 

[mak0316]

this is what I used.

strFile = ThisWorkbook.Path & "H:\Test.txt" 'file path and name

When I hover over ThisWorkbook.path is shows the correct path then I moved the test file to the H: dive root and still the same error I have tired to move the file to different locations including the same folder as the Tracking xlsm. to get the path I have been going into the file properties and copy/paste it into the code.

 

[mak0316]

I figured it out. the .txt was .Txt and it was the problem. thank you so much for your help

 

[AlphaFrog]

You're welcome. Thanks for the feedback.