1

I am using ADODB to get data from a closed excel file. I get an error when I use columns beyond column Z in my query.

Sub TestExcelWB()

Dim cn As Object
Dim szConnect As String
Dim SourceFile As String
Dim SourceSheet As String
Dim SourceRange As String
Dim rsData As Object



'-----For ADODB connection-----
SourceFile = ThisWorkbook.Path & "\ADO Test2.xlsm"

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & SourceFile & ";" & _
        "Extended Properties=""Excel 12.0;HDR=No"";"

Set cn = CreateObject("ADODB.Connection")
cn.Open szConnect
'---^^



'-----For Recordset-----
Set rsData = CreateObject("ADODB.Recordset")

SourceSheet = "Sheet1"
SourceRange = "Z1:AA10"
szSQL2 = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"

rsData.Open szSQL2, cn, 0, 1, 1
'---^^



cn.Close

End Sub

When I change the variable SourceRange = "A1:Z10" then there is no error. Is this a limit of the provider. Is there a way around this?

Community
  • 1
  • 1
Floben Moro
  • 56
  • 2
  • Code works for me. (I am using Excel 2016, but I haven't heard of this issue before even in earlier versions.) Is there something about your source data that is causing the problem? – YowE3K Jan 17 '18 at 07:14
  • After a bunch of trial and error I have found out that left side column can be set at a maximum = "H" (8th column). The right column can be a maximum = "IV" (256th column). – Floben Moro Jan 17 '18 at 08:08
  • For excel versions beyond 97-2003, left column can be set at maximum = "D" (4th column). – Floben Moro Jan 17 '18 at 08:15
  • Yes - there is a maximum of 256 columns (16-bit hangover, just like [my question](https://stackoverflow.com/q/38650103/6535336) caused by row 65536), but there is no reason why Z:AA shouldn't work. That's only two columns. – YowE3K Jan 17 '18 at 08:15
  • You originally said that just selecting column Z worked for you. Has that now stopped working? – YowE3K Jan 17 '18 at 08:17
  • I'm new to this. I will check further. I don't see any problem with the source data. – Floben Moro Jan 17 '18 at 08:18
  • Yes it stopped working. or maybe I have just overlooked previously. I'm not sure what is happening. I'll try a different computer later. I'm still doing tons of trial and error. – Floben Moro Jan 17 '18 at 08:20
  • Set your "ADO Test2.xlsm" spreadsheet up to just have data in cells Z1:AA10 - just something simple like the numbers 1 to 10 in column Z and 11 to 20 in column AA. See if that works with the code you posted. If that does work (that's what worked for me), try gradually changing your data to be what is in your real data sheet, e.g. add extra rows if that is in your real data, add extra columns to the left, add extra columns to the right, add headers, etc, etc. Try to see when it breaks. Then reverse that change and see whether it still works. That will narrow the problem. – YowE3K Jan 17 '18 at 08:24
  • wow that just worked. Thank you YowE3k. I didn't know having data in the cell have an effect. – Floben Moro Jan 17 '18 at 08:26
  • Ohh - you were trying without data?!? Yes, that would **definitely** have an effect. – YowE3K Jan 17 '18 at 08:42
  • It has data. But only up to column H. Maybe I have pasted some data up to Z during the first run. I made lots of trial and error and I lost track of what I'm changing. Because I succeeded in setting the right side column beyond H (even without data) it made me believe that data in the sheets doesn't have any effect. – Floben Moro Jan 17 '18 at 09:00

0 Answers0