Problems with incremental Function in Macro

[yan nian]

i've faced an issues with incremental functions. My current excel macro can't generate incremental number by +1. I will explain in more details in the following.
Firstly, i have a macro files(file named: Still working on it.xlsm) there are one button inside this macro files with assign macro vba the function of this macro will actually convert a file (file named: File Header.xls) into certain formats in text files(PaymentFile01.txt).
These are the following output in text files
00|MAABBJQQ|AJ_20161206_001||||||||||||||||||||||||||
I need to make it incremental like this whenever it convert to the text files (PaymentFile01.txt). This paymentfile01.txt that contains AJ_20161206_001 will be removed as this AJ_20161206_002 will be generated into the same path
00|MAABBJQQ|AJ_20161206_002||||||||||||||||||||||||||

i do have a sample on how to do this, it's based on Date( Sample function that i need.xlsm),but i can't integrate it into my code in Still working on it.xlsm because it's different situation as it seems to be more complicated.
Still working on it.xlsm codes

Sub CreatePFHeaderFooter()

    Dim x As Long, y As Long
    Dim data(1 To 29) As String
    Dim myfile As String
    'file location
    myfile = "C:\Users\lye.yan.nian\Desktop\File Header.xls"
    Application.Workbooks.Open Filename:=myfile
    DatFile1Name = ThisWorkbook.Path + "\PaymentFile01.txt"
    Open DatFile1Name For Output As #1    'create csv file

    x = 2
    While Cells(x, 1).Value <> ""

        If Cells(x, 3) = "" Then Cells(x, 3) = getNewID(Cells(x - 1, 3))

        For y = 1 To 28
            data(y) = Cells(x, y)
        Next

        Print #1, Join(data, "|")
        x = x + 1
    Wend

    Close #1
    MsgBox ("File PaymentFile01.TXT created")
    ActiveWorkbook.Close
End Sub

Function getNewID(OldID As String) As String
    Dim arr() As String, strDate As String
    Dim d As Date

    arr = Split(OldID, "_")
    strDate = arr(1)
    d = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))

    If d = Date Then
        arr(2) = Format(CInt(arr(3)) + 1, "000")
    Else
        arr(1) = Format(Date, "yyyymmdd")
        arr(2) = "001"
    End If

    getNewID = Join(arr, "_")
   
End Function

Sample function that i need.xlsm

Private Sub Workbook_Open()

If [B1] = "" Then
    [B1] = Format(Now(), "dd/mm/yyyy")
    [B2] = 1
Else
    If Trim([B1]) <> Format(Now(), "dd/mm/yyyy") Then
        [B1] = Format(Now(), "dd/mm/yyyy")
        [B2] = 1
    Else
        [B1] = Format(Now(), "dd/mm/yyyy")
        [B2] = [B2] + 1
    End If
End If

End Sub

i've uploaded 4 files
1. Still working on it.xlsm
2. File Header.xls (still working on it.xlsm will process this file)
3. PaymentFile01.txt (The output)
4. Sample function that i need.xlsm

 

[yan nian]

This paymentfile01.txt that contains AJ_20161206_001 will be removed(take this as a condition as it will be removed manually and it's not going to be written in VBA) as this AJ_20161206_002 will be generated into the same path

 

[yan nian]

hi guys, is there anyone ?

 

[jindon]

These are the following output in text files
00|MAABBJQQ|AJ_20161206_001||||||||||||||||||||||||||
I need to make it incremental like this whenever it convert to the text files (PaymentFile01.txt). This paymentfile01.txt that contains AJ_20161206_001 will be removed as this AJ_20161206_002 will be generated into the same path
00|MAABBJQQ|AJ_20161206_002||||||||||||||||||||||||||

Sub test()
    Dim fn As String, txt As String, myVal, temp
    fn = Application.GetOpenFilename("TextFiles,*.txt")
    If fn = "False" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "_(\d+)(?=\|)"
        myVal = Format$(.Execute(txt)(0).submatches(0) + 1, "_000")
        txt = .Replace(txt, myVal)
        Open Replace(fn, ".txt", "_Increment.txt") For Output As #1
            Print #1, txt
        Close #1
    End With
End Sub

 

[yan nian]

Oh my, this is exactly what i want. Thank you so much jindon

 

[yan nian]

hi Jindon, i have a slightly problem, whenever i generate this incremental numbers, there will be a blank row created under the line. For let say,
the text file contains only this
00|MAABBJQQ|AJ_20161206_001||||||||||||||||||||||||||
When i press 5 times for my button to make my data increment there will be
00|MAABBJQQ|AJ_20161206_005||||||||||||||||||||||||||
//blank row1
//blank row2
//blank row3
//blank row4
//blank row5

How am i going to prevent the blank row being created or is there anyway to delete this blank row in the text file?

 

[jindon]

Try change to

Sub test()
    Dim fn As String, txt As String, myVal, temp
    fn = Application.GetOpenFilename("TextFiles,*.txt")
    If fn = "False" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "_(\d+)(?=\|)"
        myVal = Format$(.Execute(txt)(0).submatches(0) + 1, "_000")
        txt = .Replace(txt, myVal)
        .Pattern = "(\r\n)+$"
        Open Replace(fn, ".txt", "_Increment.txt") For Output As #1
            Print #1, .Replace(txt, "")
        Close #1
    End With
End Sub

If it doesn't work, need to see your file.

I will be out, so reply will be late this afternoon.

 

[yan nian]

jindon, i found out the part that keep generating the blank column.
I used the first version of your code.

Print #1, txt; //add a semicolon at the last part, then solved.

it's like surpressing a new line from being insert.

 

[jindon]

Print #1, txt; //add a semicolon at the last part, then solved.

it's like surpressing a new line from being insert.

That shouldn't make any difference for the sake of the other viewers.