2

I have an interesting issue when querying Excel data through VBA in Excel 2010.

I am using these drivers to connect to xls or xls(x|m) files:

Sub OpenCon(ByRef theConn As Connection, ByVal FilePath As String)
'Opens the connection based on Excel version

If Right(FilePath, 4) = ".xls" Then
    theConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
       "DriverId=790;" & _
       "Dbq=" & FilePath & ";" & _
       "DefaultDir=" & FilePath
Else
    theConn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
       "DriverId=790;" & _
       "Dbq=" & FilePath & ";" & _
       "DefaultDir=" & FilePath
End If
End Sub

Now if I have data in Sheet1 from A5:O15000 and I want to query it I'd do something like

Dim rs as Recordset
Dim con as Connection
Set con = New Connection
OpenCon con, ThisWorkbook.FullName
Set rs = New Recordset
Set rs = con.Execute("SELECT * FROM [Sheet1$A5:O15000]")

Notice that my data starts at the fifth row. Now 15.000 rows doesn't pose a problem, but the moment we get over the good ol' 65k rows of Excel 2003 the code breaks. So if I had [Sheet1$A5:O100000] in there the code wouldn't run complaining the data reference is invalid.

Another interesting thing I found out is that if the data starts at A1 and I only say [Sheet1$] the same code (even for 100.000 rows) runs properly. However the file I'm using is pre-configured and filled with lots of macros so I cannot just move the data to the start of the sheet. Copying the data to a new blank sheet and querying that also isn't an option, I'm trying to use SQL to get some speed improvements and copying the whole dataset out sure won't help me.

Anyway the question is - is there another driver that would support the extra amount of rows? Or any other way to bypass this issue?

xificurC
  • 1,168
  • 1
  • 9
  • 17
  • I don't think this is a driver issue; it looks to be a limit of the Excel Range object. See: http://stackoverflow.com/questions/21837689/import-excel-range-of-more-than-65000-lines-to-access – rskar Mar 21 '14 at 14:51
  • @PortlandRunner - I am querying an xlsm file which should be obvious since I'm talking about having 100.000 rows of data. – xificurC Mar 21 '14 at 15:02
  • @PortlandRunner - I didn't mean to sound harsh or what, I just thought the description contained answers to all your questions. Since I am connecting to `ThisWorkbook` the source and destination workbooks are the same. – xificurC Mar 22 '14 at 07:27

0 Answers0