Lil Stinker
Board Regular
- Joined
- Feb 16, 2022
- Messages
- 152
- Office Version
- 2019
- Platform
- 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?
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