Help with ADO

[mvidas]

I am really hoping someone can help me with this, it has been bugging me.

I'm using the Jet 4 driver to 'query' a closed excel file (to get cell data from a closed workbook). This works great, as long as I have something in the first row and column. The reason, it seems, is that the ado connection is using the usedrange of the worksheet as the source table. If I have data in A1:J10, then counting fields/rows works great.
However, if I have data in B2:J10, it thinks there are only 9 rows/columns. So If the data on the sheet is in B2:J10 and I ask for B2, it really gives me C3's value (the second column and second row of data).

I'm attaching a couple sample files, ado.xls should remain closed as that is the one being queried. The adohelp.xls is the file to be opened.

Once you open it, you may need to change the location of ado.xls on the worksheet, unless you unzip this to the root C:\

My question, I guess, is there a way around this? Is there something I can use to retrieve the cell's address without using the excel object (which I could use to verify), or force it to pull starting in A1 even if row 1 or column A contains no data?
I tried adding HDR=NO in the extended properties, and I also tried using A1:IV65536 in the select statement; neither works as I need it to.

Any help would be greatly appreciated :)
Matt

[gnod]

Maybe, you can use this code to read from closed workbook. I forgot where did i get this code.. :dunno

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As Variant) As Variant
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim strConnection As String, strMyprompt As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"

Set cnn = New ADODB.Connection
On Error GoTo ErrorHandler
cnn.Open strConnection
Set rst = cnn.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rst.GetRows 'returns a two dim array with all records in rst
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
On Error GoTo 0
Exit Function

ErrorHandler:
strMyprompt = "The Source File or Source Range is invalid!"

MsgBox strMyprompt, vbCritical, "Error: Consolidation"
Set rst = Nothing
Set cnn = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

'Terminates execution
End
End Function

[mvidas]

Hi gnod,

Unfortunately it acts the same way, the only difference between your function and mine is that yours returns an array and mine is for a specific cell.. but using Debug.Print ReadDataFromWorkbook("C:\ado.xls", "Sheet1$A1:IV65536")(1, 1) '2-2
Debug.Print ReadDataFromWorkbook("C:\ado.xls", "Sheet two$A1:IV65536")(1, 1) '1-1I put the desired results in comments there, but they're both returning 2-2
Thanks though

[Ken Puls]

Matt, using your worksheet, placing the following in the Immediate window:

? Range("C1").formula & "=" & getclosedworkbookcell(Range("A2").Value,range("C1").value)

Returns:
="sheet1'!b"&COLUMN(A1)=2-1

Isn't that the correct answer?

[mvidas]

Hi Ken,

In my ADO.xls book, the results for Sheet1 are being returned correctly (as sheet1 has data starting in A1). However for 'sheet two' the data starts in B2 (containing "1-1")

The formula:
=getclosedworkbookcell("C:\ado.xls","sheet1!b2")
returns "2-2" correctly.

The formula:
=getclosedworkbookcell("C:\ado.xls","sheet two!b2")
returns "2-2" incorrectly (should be "1-1", i mistyped that in my "should be" part of my adohelp.xls), as 'sheet two'!b2 has "1-1" in it

I think I explained it poorly above

[Ken Puls]

Hmmm... interesting...

It looks to me like ADO is interpreting where the data table is an using it. Actually, that's kind of cool. You can fool it, although it seems a little hokey to do so. Go to Sheet Two and put a space (or anything) in A1. Save it and try it then.

[mvidas]

I know putting a blank in A1 will work for it, I would prefer to avoid that.

What is strange to me now, is that if I change the sql statement to be
"select * from [sheet two$b2:b2]", re-add "HDR=NO;" to extended properties), add "vResults()" to the dim statement above, and use "vresults=xlrs.getrows", vresults(0,0) is returning the correct "1-1" value.

However, when I change it to "select * from [sheet two$b1:b1]" (b1 being a blank cell), the recordset is returned, xlRS.EOF is returning False, but trying xlrs.getrows or xlrs.recordcount is causing excel to hang.

This is getting frustrating!
Maybe I should work on something else for the time being

[mvidas]

To explain further what I did, here is the updated function:Function GetClosedWorkbookCell(ByVal vPathFile As String, ByVal vCellRef As String)
Dim xlConn As Object
Dim xlRS As ADODB.Recordset 'Object
Dim xlSheets As Object, SheetFound As Boolean
Dim i As Long, SkipIt As Boolean, vResults()
Dim vSheet As String, vCell As String

i = InStr(vCellRef, "!")
If i = 0 Then Exit Function
vSheet = Replace(Left(vCellRef, i - 1), "'", "")
vCell = Replace(Mid(vCellRef, i + 1), "$", "")
If Len(Dir(vPathFile)) = 0 Then
GetClosedWorkbookCell = "Bad path Or file name"
Exit Function
End If

Set xlConn = CreateObject("ADODB.Connection")
With xlConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1;HDR=NO;"
.Open vPathFile
End With

Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
Do While Not xlSheets.EOF
If LCase(Replace(Replace(xlSheets.Fields("TABLE_NAME").Value, "$", ""), _
"'", "")) = LCase(vSheet) Then
vSheet = Replace(Replace(xlSheets.Fields("TABLE_NAME").Value, "$", ""), _
"'", "")
SheetFound = True
Exit Do
End If
xlSheets.MoveNext
Loop
xlSheets.Close
If Not SheetFound Then
GetClosedWorkbookCell = "Sheet """ & vSheet & """ not found"
Exit Function
End If

strSql = "SELECT * From [" & vSheet & "$" & vCell & ":" & vCell & "]"
Set xlRS = CreateObject("ADODB.Recordset")
xlRS.Open strSql, xlConn, 1, 1 '1, 1 = adOpenKeyset, adLockReadOnly
If xlRS.EOF Then
GetClosedWorkbookCell = ""
Else
Debug.Print xlRS.RecordCount
vResults = xlRS.GetRows
GetClosedWorkbookCell = vResults(0, 0)
End If

xlRS.Close
xlConn.Close
Set xlConn = Nothing
Set xlRS = Nothing
End Function
It is working correctly for the B2 cell (on both sheets), but now when I try it with B1, it hangs on sheet two :(

[SamT]

Jet sees UsedRange.TopLeft as RangeA1
You just need to convert vCell to an offset from UsedRange.TopLeft.

ie. If UsedRange.TopLeft =C4 and you want to query D6, then Query A1(Offset(2,1))

SamT :dunno

[mvidas]

Yeah that is what I've come to realize. Unfortunately to get usedrange.topleft you would have to implement the excel object (or do you?)

When the topleft is B2, any idea why the recordset returned from "SELECT * From [Sheet two$B1:B1]" has .eof=false, but wont let me check anything else?

[stanl]

Maybe I'm lost here... but does not

oRS.Open "Select * from [Sheet2$B2:J10]", oConn, adOpenStatic

work with a recordset object, assuming you know the filled range in advance and oConn = connection object to closed workbook.:dunno Stan

[stanl]

Oh, and before I am completely trashed by my last post... in Excel 2003 they finally added methods to create xml files/db's... I am referencing a post from XL-Dennis, who is one of the kindest persons I know to give credit to others. The post is concerened with open-files, but methinks it could work with closed ones... or the point being... you should be able to open and interrorgate any Excel cell, whether the file is open or closed.

http://www.ozgrid.com/forum/showthread.php?t=27818

[mvidas]

Maybe I'm lost here... but does not

oRS.Open "Select * from [Sheet2$B2:J10]", oConn, adOpenStatic

work with a recordset object, assuming you know the filled range in advance and oConn = connection object to closed workbook.:dunno Stan
That works great, if you know what the filled range is. I'm trying to build this function to retrieve data from any closed workbook. It is working great now, as long as the desired cell is in that used range. Trying to get a cell outside of the usedrange is when it is locking up on me (though oRS.EOF returns false, as does oRS Is Nothing)


Oh, and before I am completely trashed by my last post... in Excel 2003 they finally added methods to create xml files/db's... I am referencing a post from XL-Dennis, who is one of the kindest persons I know to give credit to others. The post is concerened with open-files, but methinks it could work with closed ones... or the point being... you should be able to open and interrorgate any Excel cell, whether the file is open or closed.

http://www.ozgrid.com/forum/showthread.php?t=27818
Good information there! Doesnt necessarily help me here (for any workbook, plus I only have excel 2000, which I can still create xml from using vba :)) Dennis is a very nice and knowledgeable guy, his com add-in stuff has helped me immensely (among many other subjects)

[stanl]

My confusion is 'a cell with a value outside the usedrange' appears one of those statements that seems to contradict itself. It appears either you want to treat an entire worksheet as a recordset and extract a value as row/col ----> recno()/column

I found this code snippet which avoids ADO and permits you to get a specific cell w/out leaving a 'link' from a closed workbook and could easily be parametized.



Sub GetClosedBookValue()
ThisWorkbook.Worksheets("Sheet1"). _
Range("A2").Formula = _
"='D:\[MMULT_EX.XLS]Sheet1'!$A$1"
ThisWorkbook.UpdateLink ("D:\MMULT_EX.XLS")
thePerson = ThisWorkbook.Worksheets("Sheet1"). _
Range("A2").Value
ThisWorkbook.Worksheets("Sheet1").Range("A2"). _
Formula = thePerson
End Sub


Stan

[stanl]

I played around with ADO some more, and it when you have multiple values with Extended Properties= they must be enclosed in quotes, so you might try your code with



.Properties("Extended Properties") = ""Excel 8.0;IMEX=1;HDR=No;""


I did and was able to extract single cells from sheet1 or sheet two, by either range name or [sheet two$B2:B2] and get the correct values.

Stan

[SamT]

Unfortunately to get usedrange.topleft you would have to implement the excel object (or do you?)


I dunno :dunno in Excel97, I can get

Application.Selection.CurrentRegion.Select

to select the whole table, but then I hit a wall.

I merely recorded a Macro;

Select Cell in table, Edit/Go To/ "CurrentRegion", End Macro.

SamT :dunno

[stanl]

As a tangent, were you to use the Microsoft Excel Driver and included FirstRowHasNames=0 (the equivalent of HDR=No) it would fail as this is ignored by the driver and is an acknowledged 'bug'. Stan

[mvidas]

My confusion is 'a cell with a value outside the usedrange' appears one of those statements that seems to contradict itself. It appears either you want to treat an entire worksheet as a recordset and extract a value as row/col ----> recno()/column
I don't always have an easy time explaining myself, so I can see the confusion. 'a cell with a value outside the usedrange' should have read something like "a cell's value outside the usedrange", which the value would be blank. I originally tried treating the entire worksheet as a recordset and then extract the row/col, but changed it to only treat the specific cell as the recordset (vcell & ":" & vcell)
My thought process changed halfway through this thread, so I can easily see how you got mixed up :)


I found this code snippet which avoids ADO and permits you to get a specific cell w/out leaving a 'link' from a closed workbook and could easily be parametized.



Sub GetClosedBookValue()
ThisWorkbook.Worksheets("Sheet1"). _
Range("A2").Formula = _
"='D:\[MMULT_EX.XLS]Sheet1'!$A$1"
ThisWorkbook.UpdateLink ("D:\MMULT_EX.XLS")
thePerson = ThisWorkbook.Worksheets("Sheet1"). _
Range("A2").Value
ThisWorkbook.Worksheets("Sheet1").Range("A2"). _
Formula = thePerson
End Sub


Stan
This is interesting.. I'm trying to use this as a UDF to work like indirect but for closed workbooks. I'm gonna have to play around with this, but I think for me to get something like this to work, I would have to create another instance and put that into a cell in the new instance (as UDFs cant modify worksheets otherwise).

[mvidas]

I played around with ADO some more, and it when you have multiple values with Extended Properties= they must be enclosed in quotes, so you might try your code with



.Properties("Extended Properties") = ""Excel 8.0;IMEX=1;HDR=No;""


I did and was able to extract single cells from sheet1 or sheet two, by either range name or [sheet two$B2:B2] and get the correct values.

StanWhat about B1:B1 from sheet two?
The way I have it (not surrounded by extra double quotes) seems to work (the hdr=no portion does at least)


As a tangent, were you to use the Microsoft Excel Driver and included FirstRowHasNames=0 (the equivalent of HDR=No) it would fail as this is ignored by the driver and is an acknowledged 'bug'. StanI saw this too.. gotta love MS!

[mvidas]

Unfortunately to get usedrange.topleft you would have to implement the excel object (or do you?)


I dunno :dunno in Excel97, I can get

Application.Selection.CurrentRegion.Select

to select the whole table, but then I hit a wall.

I merely recorded a Macro;

Select Cell in table, Edit/Go To/ "CurrentRegion", End Macro.

SamT :dunno
This is true, though I would have to open that workbook in order to get to this, which is what I'm trying to avoid

[stanl]

What about B1:B1 from sheet two?


Obviously I'm guilty of a Forest/Trees mentality on this one and I apologize. But I like messing with ADO and this is what I found. Assume I am going to open up a Recordset Object with a connection string {cConn} that includes



Extended Properties=""Excel 8.0;HDR=No;IMEX=n;MAXSCANROWS=n1""


where n can be 0-2 and n1 can be 0-16. further my Recordset is opened with {note I renamed Sheet2 from Sheet Two}



oRS.Open "SELECT * FROM [Sheet2$B1:B1];",cConn,1,1,1


I discovered setting various values for n/n1 gave different results... which was scary. I observed oRS.Field(0).Type which always gave 202 whether cell B1 was null or filled with a space {in which case the query worked, but you already knew that :yes }.

However, if the cell was null, oRS.GetString() would hang and error as 'Provider Does Not Have Enough Space To Complete Operation"

So for the heck of it I tried



oRS.Open "SELECT * FROM [Sheet2$B1:B1] WHERE f1 IS NULL;",cConn,1,1,1


which returned my results, and when tested on B2:B2 returned eof(). And I said to myself, 'That is about the most dumb thing!!!', and decided to post anyway:dunno

Stan

[mvidas]

Hmmm.. hadn't thought about trying 'where f1 is null', thats a good idea! Do that, and if eof then run the normal query, otherwise return blank.
I'm about to leave for the day (I hope), so unless I get motivated later on (bowling league tonight) I'll try this tomorrow.

You're lucky that you got an error after excel hung on you, I always have to manually end the instance.

And no worries on the forest/trees part, anyone who can follow my train of throught from the beginning of this thread to the end deserves a medal (wait.. this thread? any thread of mine!)

[mvidas]

Genius!
Function GetClosedWorkbookCell(ByVal vPathFile As String, ByVal vCellRef As String)
Dim xlConn As Object
Dim xlRS As Object
Dim xlSheets As Object, SheetFound As Boolean
Dim i As Long, SkipIt As Boolean, vResults()
Dim vSheet As String, vCell As String

i = InStr(vCellRef, "!")
If i = 0 Then Exit Function
vSheet = Replace(Left(vCellRef, i - 1), "'", "")
vCell = Replace(Mid(vCellRef, i + 1), "$", "")
If Len(Dir(vPathFile)) = 0 Then
GetClosedWorkbookCell = "Bad path Or file name"
Exit Function
End If

Set xlConn = CreateObject("ADODB.Connection")
With xlConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1;HDR=NO;"
.Open vPathFile
End With

Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
Do While Not xlSheets.EOF
If LCase(Replace(Replace(xlSheets.Fields("TABLE_NAME").Value, "$", ""), _
"'", "")) = LCase(vSheet) Then
vSheet = Replace(Replace(xlSheets.Fields("TABLE_NAME").Value, "$", ""), _
"'", "")
SheetFound = True
Exit Do
End If
xlSheets.MoveNext
Loop
xlSheets.Close
If Not SheetFound Then
GetClosedWorkbookCell = "Sheet """ & vSheet & """ not found"
Exit Function
End If

strSql = "SELECT * From [" & vSheet & "$" & vCell & ":" & vCell & _
"] where f1 is null;"
Set xlRS = CreateObject("ADODB.Recordset")
xlRS.Open strSql, xlConn, 1, 1 '1, 1 = adOpenKeyset, adLockReadOnly
If xlRS.EOF Then
xlRS.Close
strSql = "SELECT * From [" & vSheet & "$" & vCell & ":" & vCell & "]"
xlRS.Open strSql, xlConn, 1, 1 '1, 1 = adOpenKeyset, adLockReadOnly
vResults = xlRS.GetRows
GetClosedWorkbookCell = vResults(0, 0)
Else
GetClosedWorkbookCell = ""
End If

xlRS.Close
xlConn.Close
Set xlConn = Nothing
Set xlRS = Nothing
End FunctionI'll give it a real thorough testing tomorrow, but I think you did it!

UPDATE:
Do you mind if I use your full last name while thanking you (I do know it), or would you prefer I just use your screen name?

[stanl]

UPDATE:
Do you mind if I use your full last name while thanking you (I do know it), or would you prefer I just use your screen name?


Thanks appreciated, and perhaps my opportunity to return the thanks as I would appreciate your .02 on my MSI Database Export post... a few days back.

Stan