Hi and thanks in advance,
I am using Excel 2010 and have some vba that writes values in a column to a text file. When I write to the file I enclose each value in double quotes. I then want to run some SQL on the text file to return a count of instances where there is no data, where there is just the two double quotes ... "". I have tried a number of variations of WHERE criteria but I always get back a count of zero despite there being a couple of rows in the text file which contain the two double quotes ""
Here is the SQL ...
I am using Excel 2010 and have some vba that writes values in a column to a text file. When I write to the file I enclose each value in double quotes. I then want to run some SQL on the text file to return a count of instances where there is no data, where there is just the two double quotes ... "". I have tried a number of variations of WHERE criteria but I always get back a count of zero despite there being a couple of rows in the text file which contain the two double quotes ""
Here is the SQL ...
Code:
Open ThisWorkbook.Path & "\777.txt" For Output As 1
Print #1, Chr(34) & "Test" & Chr(34)
For Each rng In rngSel
Print #1, Chr(34) & rng & Chr(34)
Next rng
Close #1
Set db1 = CreateObject("ADODB.Connection")
db1.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=" & ThisWorkbook.Path & ";"
strSQL = "SELECT Count([Test]) as myCount "
strSQL = strSQL & "FROM [777.txt] "
strSQL = strSQL & "WHERE [Test] = '" & Chr(34) & Chr(34) & "'"