Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

**DocAElstein** · 05-28-2020, 02:56 PM

Sub jindonsTesties()
' PART 2 ================================

This part of jindon's offering is concerned with manipulating the text file. It has very little to do with Excel or VBA. The VBA coding is using ADO / ADODB techniques, which are like "SQL" stuff, that is universal and not native to Excel or VBA
https://en.wikipedia.org/wiki/ActiveX_Data_Objects
https://en.wikipedia.org/wiki/SQL
https://www.excelforum.com/excel-pro...liarities.html
https://www.excelforum.com/excel-pro...ml#post4664487

Rem 3
'3b) This makes a copy of the original text file. I am not sure why.

Rem 4 ADODB stuff
ADODB stuff allows us to use universal language to look at text files, and other simple data values files

'4a) this does something similar to out VBA Open ___ As ___ , and basically sets up a route, in this case to a folder.

'4b) This builds an object which can approximately be thought of as a text file, except we can get at the data in a number of ways, which includes selecting based on criteria. The criteria string developed in Part 1 _…
[color=code](Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100) [/code]
_… is a syntax recognised to select all records ( rows ) based on Not having the values in the second filed ( column B )

I think this line, Rs.GetString(, , ",", vbCrLf) , appears to return our record ( row ) set in our familiar text file single long string format.

Rem 6 will make a text file from the long string in the way in which we are familiar.

( I am not sure why jindon is making various copies with different names, other than that such behaviour is sensible when developing such coding so that there are plenty of back up copies )

Code:

' Part 2 ===============================================================================
'End Sub
'Sub MyTests_CreateNew()
Rem 3 source text file
'3a) source text file
Dim myCSV As String ' , txt As String
 Let myCSV = ThisWorkbook.Path & "\Alert 29May excelforum..csv" ' "C:\Users\WolfieeeStyle\Desktop\Alert..csv"
' Call CreateNew(myCSV, Mid$(txt, 5))
'End Sub
'Private Sub CreateNew(myCSV As String, txt As String)
    Dim fn As String ' , cn As Object, rs As Object, x
' 3b Make copy of test file , make temporary file
    fn = Left$(myCSV, InStrRev(myCSV, "\")) & "tempComma.csv"
Dim PathAndFileName As String: Let PathAndFileName = fn
 FileCopy myCSV, fn ' FileCopy source, destination         https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filecopy-statement

Rem 4 ADODB stuff
'4a)
Dim Cn As Object: Set Cn = CreateObject("ADODB.Connection")
    With Cn
     .Provider = "Microsoft.Ace.OLEDB.12.0"
     .Properties("Extended Properties") = "Text;HDR=No;"
        '.Open Left(fn, InStrRev(fn, "\"))
Dim PathOnly As String: Let PathOnly = Left(fn, InStrRev(fn, "\"))
     .Open PathOnly
    End With
'4b)
 Let txt = Mid$(txt, 6)  '  (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)
Dim Rs As Object: Set Rs = CreateObject("ADODB.Recordset")
 Rs.Open "Select * From [tempComma.csv] Where " & txt, Cn, 3
Dim x As String
 Let x = Rs.GetString(, , ",", vbCrLf): Debug.Print x

 Set Cn = Nothing: Set Rs = Nothing
Rem 5
 Kill fn
Rem 6
 Open Replace(myCSV, ".csv", "_Filtered.csv") For Output As #1
 Print #1, x;
 Close #1
End Sub