PDA

View Full Version : remove line or lines from a text file using vba if checkbox is true



cmccabe1
09-06-2016, 11:57 AM
In the vba below I am trying to remove line or lines in a text file if a corresponding checkbox is true. Thank you :)/

vba


Dim lines() As String
Dim outputlines As New List(Of String)
Dim searchString As String = Checkbox2:Checkbox5 = True
lines = IO.File.ReadAllLines("N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"sample_descriptor.txt")


For Each line As String In lines
If line.Contains(searchString) = False Then
outputlines.Add(line)
End If
Next

Kenneth Hobs
09-07-2016, 06:09 AM
The code that you posted is VSTO or vb.net, not VBA. Did you want an equivalent VBA? The checkbox selected would check for any string in the column above it?

cmccabe1
09-07-2016, 06:18 AM
Yes, that would be great.

For example if the checkbox in B14:E14 are checked, then the line in the sample_descriptor is removed.... B14 removes line 1 (B2:B12), C14 removes line 2 (C2:C14), D14 removes line 3 (D2:12), E14 removes line 4 (E2:E14). Thank you :).

Kenneth Hobs
09-07-2016, 07:03 AM
By line, I am not sure what you mean.

Can you attach a sample txt file? Maybe even one that has say the first one, b2:b12 removed?

cmccabe1
09-07-2016, 07:26 AM
In the below text file the header row is hard coded, then there are 4 lines after that:
line 1 - B2:B14
line 2 - C2:C12
line 3 - D2:12
line 4 - E2:E12

so:
if the checkbox in B14 is checked then line1 is removed
if the checkbox in C14 is checked then line2 is removed
if the checkbox in D14 is checked then line3 is removed
if the checkbox in E14 is checked then line4 is removed.

Does this help and thank you very much :).

sample_descriptor.txt
Experiment Sample Control Sample Display Name Gender Control Gender Spikein Location Barcode Medical Record Date of Birth Order Date
line1 from (B2:B14)
line2 from (C2:C14)
line3 from (D2:14)
line4 from (E2:E14)


sample_descriptor.txt if checkbox in B14 is checked
Experiment Sample Control Sample Display Name Gender Control Gender Spikein Location Barcode Medical Record Date of Birth Order Date
line2 from (C2:C14)
line3 from (D2:14)
line4 from (E2:E14)

Kenneth Hobs
09-07-2016, 08:29 AM
So the values of B2:B14 means nothing? You just want the 2nd line or 1st record of the text file deleted if the checkbox in column 2 (column B) is checked? If this is the case, then when checkbox in column C is checked, it would remove the 3rd line which is not really the 3rd line anymore if the 2nd line was removed. If this is the case, you may want to run the macro by a Command Button so that all checkboxes can be addressed at once rather than after each is checked.

cmccabe1
09-07-2016, 08:39 AM
Yes, you are correct in that the lines 1-4 in the text file need to be removed if the user has selected the checkbox B14 for line1, C14 for line 2, D14 for line3, E14 for line4. Since the checkbox must be checked when the file the first time, I don't think a command button is needed because the user will select whatever checkbox at one time. They will never go back and checj them at a later time. Will the document need to be saved in order for the line to be removed? Thank you :).

Kenneth Hobs
09-07-2016, 09:47 AM
Do you understand that a click of the checkbox would fire a macro for each one individually? After each file modification, line deletion, it would do it and then save the file.

That's a problem. e.g.
File data is:
1
2
3
4

1=row/record of Column/Field names, never to be removed.

(1) Column B checkbox selected. Data is now:
1
3
4

(2) Column C checkbox selected Data is now:
1
3

Though it should be?:
1
4
That is what a Command Button's result would be.

cmccabe1
09-07-2016, 10:00 AM
That makes sense, I wasn't understanding but now I do. Maybe a user prompt asking if any lines are to be removed... if yes then which line... if no then nothing happens. Thank you :).

Kenneth Hobs
09-07-2016, 12:11 PM
There are several ways to do this.

Put this in a Module and add the reference as I commented.

Function StrFromTXTFile(filePath As String) As String
Dim str As String, hFile As Integer

If Dir(filePath) = "" Then
StrFromTXTFile = "NA"
Exit Function
End If

hFile = FreeFile
Open filePath For Binary Access Read As #hFile
str = Input(LOF(hFile), hFile)
Close hFile

StrFromTXTFile = str
End Function


Sub StrToTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(GetFolderName(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub


'Require reference: Microsoft Scripting Runtime
Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim FSO As FileSystemObject, s As String
Set FSO = New FileSystemObject
'Debug.Print fso.GetFile(filespec).path
s = FSO.GetParentFolderName(filespec)
Set FSO = Nothing
GetFolderName = s
End Function

Right click your sheet's tab, View Code, and Paste this code. Change the path and filename for txtFN to suit. Always test on backups of your xlsm and text files and such.

Private Sub CheckBox2_Click()
cMain
End Sub


Private Sub CheckBox3_Click()
cMain
End Sub


Private Sub CheckBox4_Click()
cMain
End Sub


Private Sub CheckBox5_Click()
cMain
End Sub


Private Sub cMain()
Dim s As Shape, txtFN As String, fnS() As String, fnS2() As String
Dim i As Long, j As Long

txtFN = ThisWorkbook.Path & "\cMain.txt"
If Len(Dir(txtFN)) = 0 Then
MsgBox "File does not exist:" & vbLf & txtFN, vbCritical, "Macro Ending"
Exit Sub
End If

If MsgBox("Are you sure that you want to remove lines now?", vbCritical + vbYesNo, txtFN) = vbNo Then Exit Sub

fnS() = Split(StrFromTXTFile(txtFN), vbLf)

For Each s In ActiveSheet.Shapes
'ActiveX Control
If s.Type = msoOLEControlObject Then
If Left(s.Name, 8) = "CheckBox" Then
'Debug.Print s.Name, ActiveSheet.OLEObjects(s.Name).Object.Value
i = Right(s.Name, Len(s.Name) - 8) - 1 '-1 since lbound(fns) = 0
If i <= UBound(fnS) And ActiveSheet.OLEObjects(s.Name).Object.Value = True Then fnS(i) = ""
End If
End If
Next s

'Create fnS2()=fnS() but "" valued elements removed.
j = -1
For i = 0 To UBound(fnS)
If fnS(i) <> "" Then
j = j + 1
ReDim Preserve fnS2(0 To j)
fnS2(j) = fnS(i)
End If
Next i
'MsgBox Join(fnS2, vbLf)

'Overwrite txtFN
StrToTXTFile txtFN, Join(fnS2, vbCrLf)
End Sub

cmccabe1
09-15-2016, 05:59 AM
I added these to the code below and am getting an expected End Sub after the first End If statement. I added the module and tried the code as is but got the same error that I can not seem to solve. Thank you :).

VBA


' REMOVE ENTERIES CHECK '
Line10:
If CheckBox2 Or CheckBox3 Or CheckBox4 Or CheckBox5 = True Then
Call cMain
Else: GoTo Line20
End If


Private Sub cMain()
Dim s As Shape, txtFN As String, fnS() As String, fnS2() As String
Dim i As Long, j As Long

txtFN = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\" & "sample_descriptor.txt"
'ThisWorkbook.Path & "\cMain.txt"
If Len(Dir(txtFN)) = 0 Then
MsgBox "File does not exist:" & vbLf & txtFN, vbCritical, "Macro Ending"
Exit Sub
End If

If MsgBox("Are you sure that you want to remove lines now?", vbCritical + vbYesNo, txtFN) = vbNo Then Exit Sub

fnS() = Split(StrFromTXTFile(txtFN), vbLf)

For Each s In ActiveSheet.Shapes
'ActiveX Control
If s.Type = msoOLEControlObject Then
If Left(s.Name, 8) = "CheckBox" Then
'Debug.Print s.Name, ActiveSheet.OLEObjects(s.Name).Object.Value
i = Right(s.Name, Len(s.Name) - 8) - 1 '-1 since lbound(fns) = 0
If i <= UBound(fnS) And ActiveSheet.OLEObjects(s.Name).Object.Value = True Then fnS(i) = ""
End If
End If
Next s

'Create fnS2()=fnS() but "" valued elements removed.
j = -1
For i = 0 To UBound(fnS)
If fnS(i) <> "" Then
j = j + 1
ReDim Preserve fnS2(0 To j)
fnS2(j) = fnS(i)
End If
Next i
'MsgBox Join(fnS2, vbLf)

'Overwrite txtFN
StrToTXTFile txtFN, Join(fnS2, vbCrLf)