Importing data from text file, but adding data at the end of the existing data in excel.

[SteynBS]

Hi Guys, So I am using this to simplify our stock takes. Our scanners dock and download the counts in a text document. The program only writes to one file named CIPHER.OUT.txt we use multiple scanners. so after every download, it rewrites the .txt file with the last download. So I have to import the .txt data into excel every time before I dock and Download the new scanner. Importing the data is not to difficult, but I need the new import to add to the end of the data in excel.

This is the vba I use to import the .txt file data

Sub ImportTextFileToExcel()
Dim textFileNum, rowNum, colNum As Integer
Dim textFileLocation, textDelimiter, textData As String
Dim tArray() As String
Dim sArray() As String
textFileLocation = "C:\Users\200327\Desktop\CIPHER.OUT.txt"
textDelimiter = "|"
textFileNum = FreeFile
Open textFileLocation For Input As textFileNum
textData = Input(LOF(textFileNum), textFileNum)
Close textFileNum
tArray() = Split(textData, vbLf)
For rowNum = LBound(tArray) To UBound(tArray) - 1
If Len(Trim(tArray(rowNum))) <> 0 Then
sArray = Split(tArray(rowNum), textDelimiter)
For colNum = LBound(sArray) To UBound(sArray)
ActiveSheet.Cells(rowNum + 2, colNum + 1) = sArray(colNum)
Next colNum
End If
Next rowNum

End Sub

this is what it looks like after the import into excel

With the second scanner data that has over written the CIPHER.OUT.txt I want to import the new data without replacing the data in excel, but it should onlu add (in this example from row 9)
Lets assume the next data will occupy the row up until row 50. the 3rd scanner data should be added from row 51 if it makes sence. Please help

 

[Alex Blakenburg]

I don't have any data to test it on but see if this works for you.

Sub ImportTextFileToExcel()
    Dim textFileNum, rowNum, colNum As Integer
    Dim textFileLocation, textDelimiter, textData As String
    Dim tArray() As String
    Dim sArray() As String
    textFileLocation = "C:\Users\200327\Desktop\CIPHER.OUT.txt"
    textDelimiter = "|"
    textFileNum = FreeFile
    Open textFileLocation For Input As textFileNum
    textData = Input(LOF(textFileNum), textFileNum)
    Close textFileNum
    tArray() = Split(textData, vbLf)
   
    Dim shtDest As Worksheet
    Dim lrowDest As Long
   
    Set shtDest = ActiveSheet
    lrowDest = shtDest.Range("A" & Rows.Count).End(xlUp).Row
   
    For rowNum = LBound(tArray) To UBound(tArray) - 1
        If Len(Trim(tArray(rowNum))) <> 0 Then
            sArray = Split(tArray(rowNum), textDelimiter)
            For colNum = LBound(sArray) To UBound(sArray)
                shtDest.Cells(lrowDest + 1, colNum + 1) = sArray(colNum)
            Next colNum
            lrowDest = lrowDest + 1
        End If
    Next rowNum

End Sub

 

[SteynBS]

I don't have any data to test it on but see if this works for you.

Sub ImportTextFileToExcel()
    Dim textFileNum, rowNum, colNum As Integer
    Dim textFileLocation, textDelimiter, textData As String
    Dim tArray() As String
    Dim sArray() As String
    textFileLocation = "C:\Users\200327\Desktop\CIPHER.OUT.txt"
    textDelimiter = "|"
    textFileNum = FreeFile
    Open textFileLocation For Input As textFileNum
    textData = Input(LOF(textFileNum), textFileNum)
    Close textFileNum
    tArray() = Split(textData, vbLf)
  
    Dim shtDest As Worksheet
    Dim lrowDest As Long
  
    Set shtDest = ActiveSheet
    lrowDest = shtDest.Range("A" & Rows.Count).End(xlUp).Row
  
    For rowNum = LBound(tArray) To UBound(tArray) - 1
        If Len(Trim(tArray(rowNum))) <> 0 Then
            sArray = Split(tArray(rowNum), textDelimiter)
            For colNum = LBound(sArray) To UBound(sArray)
                shtDest.Cells(lrowDest + 1, colNum + 1) = sArray(colNum)
            Next colNum
            lrowDest = lrowDest + 1
        End If
    Next rowNum

End Sub

@Alex Blakenburg It works a charm, thank you. With the first data set that is imported, it leaves a space open for the heading which is perfect.( shtDest.Cells(lrowDest + 1, colNum + 1) = sArray(colNum)), but with the second data set imported, it leaves a space open between the data sets which is not ideal. anyway to overcome that?

Again thank you very much for your assistance.

anyway to

 

[Alex Blakenburg]

It doesn't do that on my test data. Does you text file have a blank line at the start ?
or a blank row at the end ?

 

[SteynBS]

I don't have any data to test it on but see if this works for you.

Sub ImportTextFileToExcel()
    Dim textFileNum, rowNum, colNum As Integer
    Dim textFileLocation, textDelimiter, textData As String
    Dim tArray() As String
    Dim sArray() As String
    textFileLocation = "C:\Users\200327\Desktop\CIPHER.OUT.txt"
    textDelimiter = "|"
    textFileNum = FreeFile
    Open textFileLocation For Input As textFileNum
    textData = Input(LOF(textFileNum), textFileNum)
    Close textFileNum
    tArray() = Split(textData, vbLf)
  
    Dim shtDest As Worksheet
    Dim lrowDest As Long
  
    Set shtDest = ActiveSheet
    lrowDest = shtDest.Range("A" & Rows.Count).End(xlUp).Row
  
    For rowNum = LBound(tArray) To UBound(tArray) - 1
        If Len(Trim(tArray(rowNum))) <> 0 Then
            sArray = Split(tArray(rowNum), textDelimiter)
            For colNum = LBound(sArray) To UBound(sArray)
                shtDest.Cells(lrowDest + 1, colNum + 1) = sArray(colNum)
            Next colNum
            lrowDest = lrowDest + 1
        End If
    Next rowNum

End Sub

Got it, blank line at the bottom. Thank you very much for your assistance. Take care and be blessed.

 

[Alex Blakenburg]

Do you want me to try and remove the blank ?
There actually must be something in it or the next xlup would not stop there.
If you go to column E on the blank row and hit ctrl+left arrow, where does it stop ?
Which column will always have data, can we use Column C instead of A to determine the last row ?

 

[SteynBS]

Do you want me to try and remove the blank ?
There actually must be something in it or the next xlup would not stop there.
If you go to column E on the blank row and hit ctrl+left arrow, where does it stop ?
Which column will always have data, can we use Column C instead of A to determine the last row ?

I removed the blank line, I will just check if the empty line persists after a scan. I doubt if it will leave a blank line. So it works. Thank you very much.
I will with time want to "upgrade" the vba by adding fancy tweaks like a msg box to confirm an upload, and maybe even an application command to manually choose the file with a msg box "choose file" instead of opening and closing it. But that is a development for later.
Again thank you very much. I cant thank you enough

 

[Alex Blakenburg]

You're welcome. Glad I could help.