VB Text Array Split Delimiter results in unwanted lines

[Lil Stinker]

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?

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

 

[jimrward]

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

 

[Lil Stinker]

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.

 

[Lil Stinker]

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:

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

 

[myall_blues]

Where is lastuserROW coming from? It is neither declared nor set.

 

[Lil Stinker]

Where is lastuserROW coming from? It is neither declared nor set.

Bugger! Sorry I was copy pasting in a hurry and missed a few lines .

Dim lastuserROW as Long
lastuserROW = .Range(“A” & Rows.Count).End(xlUp).Row