0

I have issues with a VBA project in Excel that used to work before for over a year.

My code is below, the last line is what gives the error. What I am trying to do is querying a sheet on the same workbook. I have other queries that call other workbooks but this module that calls a sheet on the same workbook is whats giving troubles.

Some key information:

  • The workbook is binary
  • The srcDataSheet value is [Data$A1:Y68277]. Of course, the row number could change but columns will always be A to Y.
  • Sheet named Data does exist.
  • Tried changing providers. strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

What could possibly be causing this error when the workbook worked perfectly for over a year? Please help.

Sub QueryDataSheet(srcDataSheet As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strFile As String, strCon As String, strSQL As String, strRangeAddress As String
Dim iCols As Long, lastRow As Long, fLastRow As Long

strFile = ThisWorkbook.FullName

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT [PLANT], [FILTER_PLANT], [Rule], [SKU], [Style], [Color], [Size Code], " & _
         "Sum(IIf([Order_Type2]='XDR', [Corrected Qty], 0)) AS XDR, Sum(IIf([Order_Type2]='XDR CONSUMED', [Corrected Qty], 0)) AS XDR_CONSUMED, " & _
         "Sum(IIf([Order_Type2]='EXCESS', [Corrected Qty], 0)) AS EXCESS, [RULE_RANGE], [I Indicator] " & _
         "FROM " & srcDataSheet & " " & _
         "WHERE [PLANT] IN ('0F','4J') " & _
         "GROUP BY [PLANT], [FILTER_PLANT], [Rule], [SKU], [Style], [Color], [Size Code], [RULE_RANGE], [I Indicator]"


rs.Open strSQL, cn 'ERROR HERE
June7
  • 19,874
  • 8
  • 24
  • 34
rzenva
  • 87
  • 1
  • 1
  • 11
  • Why would row number change? Why ms-access tag? Why use Excel as a database? ACE works for me. Cannot replicate issue. – June7 Jun 29 '20 at 18:44
  • `[Data$A1:Y68277]` did you mean `[Data!$A1:Y68277]` ? – Tim Williams Jun 29 '20 at 18:47
  • @TimWilliams, works fine without ! character. The variable is declared as an argument in procedure declaration. – June7 Jun 29 '20 at 18:49
  • @June7, Because the data the user pastes could be more or less in rows. Ms-access tag because the error says Access data base engine and it was recommended as a tag. Because users didnt want to 'lose time' in opening both access and Excel and I simply lacked the will to change their views and I already had the code mostly done from past projects. ACE? – rzenva Jun 29 '20 at 18:50
  • ACE from the alternate provider you attempted: `Provider=Microsoft.ACE.OLEDB`. – June7 Jun 29 '20 at 18:51
  • Yes - I missed the parameter... – Tim Williams Jun 29 '20 at 18:52
  • Since neither Access database nor Access VBA are actually involved, don't see relevance of ms-access tag. But an SQL statement is involved so added that tag. – June7 Jun 29 '20 at 18:53
  • Did it work when you had fewer rows? https://stackoverflow.com/questions/49543744/oledb-connection-to-support-more-than-65536-rows-update-access-from-excel-sheet – Tim Williams Jun 29 '20 at 18:55
  • Ah, I neglected to use row numbers in my code. Now I do get that error. Reduced to 65000 and code runs. – June7 Jun 29 '20 at 18:57
  • @TomSlabbaert - I deleted some rows and it worked. I changed to using "[Data$A:Y]" and this helped. – rzenva Jun 29 '20 at 19:57
  • @June7, what did you do when you had to work with more than 65K rows? – rzenva Jun 29 '20 at 19:58
  • I've never had to. I don't use Excel as a database. – June7 Jun 29 '20 at 20:22

0 Answers0