0

I have an Excel sheet with data where a column is blank for first several rows (8 rows exactly). As the provider scans first 8 rows only, the query of ADODB Recordset returns 0 in this case. But if I fill the data within first 8 rows, then the ADODB Recordset returns results and I can get the record count of the query result correctly.

I tried changing IMEX to 0,1,2 and 3 and even tried changing CursorLocation and CursorType in the below query. So I would like to know if we can skip the blank rows and start the query from non-blank row.

Sub GetRecordCount()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, StrSQL As String
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
With cn
  .Provider = "Microsoft.ACE.OLEDB.12.0"
  .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
  "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0;Readonly=True"""
End With
cn.Open
                       
Dim k As Variant

StrSQL = "select [BOOKNAME] From [BOOK DETAILS$A4:BN119] Where ([COUPONNUMBER]='ZFX' or [COUPONNUMBER]='ZDL')"
rs.Open StrSQL, cn
k = CInt(Trim(rs.RecordCount))
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Dhay
  • 585
  • 7
  • 29
  • Either change start row to `[BOOK DETAILS$A9:BN119]` or delete the first rows, it's read only, so no worries. `Rows("1:8").Delete Shift:=xlUp`. Could that work? – Loveb Jun 28 '23 at 11:22
  • Which column is blank? – Tim Williams Jun 28 '23 at 16:16
  • 1
    If ONLY the first 8 rows are blank of the column with name eg COL_WITH_BLANK, then you can add one more criterion in WHERE .... and [COL_WITH_BLANK] <> """" – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Jun 28 '23 at 16:41
  • @TimWilliams. 'COUPONNUMBER' column has blanks. – Dhay Jun 29 '23 at 01:47
  • @ΑΓΡΙΑΠΕΣΤΡΟΦΑ: Adding the criterion "AND [COL_WITH_BLANK]" didn't work. – Dhay Jun 29 '23 at 03:32
  • @Dhay replace the COL_WITH_BLANK with the name of your field> COUPONNUMBER – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Jun 29 '23 at 13:50
  • Maybe look at: https://yoursandmyideas.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/comment-page-2/#:~:text=MaxScanRows%20%3A%20Excel,its%20later%20versions – Tim Williams Jun 29 '23 at 16:33
  • And: https://stackoverflow.com/questions/3081708/excel-cell-formatting-problem – Tim Williams Jun 29 '23 at 17:24
  • @ΑΓΡΙΑΠΕΣΤΡΟΦΑ. Yes I did. – Dhay Jun 30 '23 at 02:12
  • @TimWilliams. Thanks for the links. As mentioned in those links MAXSCANROWS does not seem to affect the query. But I made a copy of the file and I put a dummy text in first row where it is blank and saved it then the query returns record count. But do you know what is weird? It works in the file where I put a dummy text and saved then deleted the text and saved again aka still the first 8 rows are blank. But the query didn't work in the original file. – Dhay Jun 30 '23 at 02:18
  • Yes - I wasn't able to replicate your problem in my testing, even with >100 empty rows – Tim Williams Jun 30 '23 at 02:34

0 Answers0