VB Text Array Split Delimiter results in unwanted lines

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
155
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
Thanks for the reply. Could you provide a code sample of what you're suggesting? I'm still learning the ropes and not sure how to attack it.
 
Upvote 0
I've done a little more digging and now I've found that it is not the delimiter or the Split function that is causing the issue after all. It appears to have something to do with how the data is being transferred to the text file. I also misstated that the extra line is coming in before the string. It actually comes in after the string but the result on the worksheet cell is the same, it looks empty. Can anyone tell me why an extra line is being added in after the final value and how to get around it?

The code I posted originally is how the data is extracted from the text file and written to the worksheet. This code below is the originator where the data is taken from an Excel file and written to a text file cell by cell.

Worksheet Change event initiates the macro synctoWRITEFILE which is located in a separate module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
''' update changes for all users (on data change, not on active sync) '''
If Not Intersect(Target, Range("A2:Y999999")) Is Nothing And Settings.Range("S9").value = False Then
   Call synctoWRITEFILE(Target.Worksheet.Name, Target.Address, Target.value)
End If
End Sub


Sub synctoWRITEFILE(shtNAME As String, cellADD As String, cellVAL As String)
Dim fileNAME As String, filePATH As String, fileTEXT As String, prevVAL As String, currUSER As String
Dim userROW As Long, txtFILE As Integer

   With Settings
      If .Range("S9").value = False Then 'run only when not on active sync
         checkSHAREFOLD 'check for correct shared folder
         shareFOLD = [shareFLDR] 'set the shared folder
         currUSER = .Range("S5").value 'set user name

         For userROW = 5 To lastuserROW 'loop through all users to add changes to their workbooks
            userNAME = .Range("U" & userROW).value 'user name
            If currUSER = userNAME Then GoTo nextUSER 'no need to add changes to current user workbook
            If Dir(shareFOLD & "\" & userNAME, vbDirectory) = "" Then MkDir (shareFOLD & "\" & userNAME) 'create user folder if one does not exist
            filePATH = shareFOLD & "\" & userNAME & "\" & shtNAME & "--" & cellADD & ".txt" 'create text file name using cell address
            txtFILE = FreeFile 'assign unique free file number
            Open filePATH For Output As txtFILE
            Print #txtFILE, currUSER & "*o*" & cellVAL 'add current user w/ delimiter & cell value
            Close #txtFILE
nextUSER:
         Next userROW
      End If
   End With
End Sub
 
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