VB Text Array Split Delimiter results in unwanted lines

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
152
Office Version
  1. 2019
Platform
  1. Windows
I'm using the following code to bring values into Excel that have been saved to a text file. I'm hitting two walls and hoping somebody could shed some light.

Problem 1: the original code as written uses vbNewLine as a delimiter which works fine to split the username from the new value... until it doesn't. It doesn't work when the new value in the text file happens to be broken into separate lines. With vbNewLine, it splits the new value after the first line and erases any data afterward.

Problem 2: so, I tried using a different delimiter in the split. The problem here is now I get an extra line before the new value when bringing the data into Excel so all of the cells appear blank because the value is actually on the next line in the cell. Even when the new value is broken into two lines, it brings in both lines of text underneath the unwanted line.

Is there a better way of handling splitting the username from the new value in the file while still allowing for multiline values?

VBA Code:
Dim fileNAME As String, filePATH As String, fileTEXT As String, prevVAL As String, textARR() As String, newVAL As String
Dim shtNAME As String, cellADD As String, cellVAL As String, 
Dim userFLDR As String
Dim txtFILE As Integer

      Do While Len(fileNAME) > 0
         filePATH = userFLDR & fileNAME
         Open filePATH For Input As txtFILE
         fileTEXT = Input(LOF(txtFILE), txtFILE)
         Close txtFILE
         textARR = Split(fileTEXT, vbNewLine) ' "*o*") w/ different delimiter   *** adds extra line before value ***
         userNAME = textARR(0) 'user who made change
         newVAL = textARR(1) 'changed value
         Close #1
         
         shtNAME = Left(fileNAME, InStr(fileNAME, "--") - 1) 'extract sheet name
         cellADD = Replace(Right(fileNAME, Len(fileNAME) - InStr(fileNAME, "--") - 1), ".txt", "") 'cell address of change
         prevVAL = ThisWorkbook.Sheets(shtNAME).Range(cellADD).value 'set current value
         ThisWorkbook.Sheets(shtNAME).Range(cellADD).value = newVAL 'make cell change

         fileNAME = Dir() 'clear file name
      On Error Resume Next
         Kill (filePATH) 'delete file
      On Error GoTo 0
      Loop
 
One approach might be as follows
Do as you are doing to pick up the username from split(0)
Next take your original string you read in and replace the username you have just found with a null
Finally take the resultant string and replace vbnewline with a null this should leave you with a text string
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top