ADO - recordset - closed excel workbook

[graham d]

Heres my query

I have a closed work book I wish to extract results from into my open
workbook

1. I know the path where the closed spreadsheet file lives
2. I know the sheet name and cell that i want to read into my recordset

however!! NB This sheet is sent to me by someone external and there are
no named ranges in it.

Ive found a code snippet that I thought might suit

Sub GetDataFromClosedWorkbook(ByVal SourceFile As String, _
ByVal SourceRange As String, _
ByVal TargetRange As Range,
ByVal IncludeFieldNames As Boolean)

..
..
..
' it set up the connection to my file

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
dbConnection.Open dbConnectionString

' and calls the execute method to run the query
Set rs = dbConnection.Execute("[" & SourceRange & "]")
..
..
End Sub


The Call works fine provided we use parameters like
Call GetDataFromClosedWorkbook("C:\mySS.xls", "F5:H7", Range("Target"),
False)


But I need to be more specific instead of saying "F5:H7" I need to say
WorkSheets("mySheetName").Range("F5:H7")

ive tried using the .Address() method to return a string
i.e. WorkSheets("mySheetName").Range("F5:H7").Address()

but to no avail

NB: The ADODB method dbConnection.Execute(.....)
expects a string


Any Ideas

Thanks

Graham









*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[keepITcool]

the SourceRange must have a format like
"sheet1$A2100"

note the $ is used as the pipe.
the rangeref must be A1 relative notation.

"Build like this...

Set rngQry = Range("A1:d100")
strqry = rngQry.Worksheet.Name & "$" & _
rngQry.Address(0, 0, xlA1)



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Ron de Bruin]

Hi graham

Maybe usuful
http://www.rondebruin.nl/ado.htm

 

[TK]

Hi graham

Try this VB ADO

Remember to Reference
Microsoft ActiveX Data Objects 2.x (I like 2.5)

Jet will not return both strings and numbers
in the same recordset so use rs for one, rs1 for
the another. You can create as many as you like

Private Sub CommandButton1_Click()

Dim DB_NAME As String
Dim DB_CONNECT_STRING As String

'You must fully quality the path to your file
'I like to locate the file with properties and
'copy paste - ADO really bitches if not perfect
'On this computer it was as follows

DB_NAME = ("C:\Documents and Settings\") _
& ("The Cat Man\My Documents\ProgressBar.xls")

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0" _
& ";Data Source=" & DB_NAME _
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Test to see if we are connected
If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & DB_NAME, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Create the recordset
Dim Rs As ADODB.Recordset
Set Rs = New Recordset
Dim Rs1 As ADODB.Recordset
Set Rs1 = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "Select * from [Sheet1$B9:B20]"
strSQL1 = "Select * from [Sheet1$A9:A10]"

'Retreive the records
Rs.CursorLocation = adUseClient
Rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic
Rs1.Open strSQL1, cnn, adOpenStatic, adLockBatchOptimistic

'Copy the records to the worksheet
Worksheets("Sheet2").Range("E2").CopyFromRecordset Rs
Worksheets("Sheet2").Range("D2").CopyFromRecordset Rs1

'Close the connection
cnn.Close
Set cnn = Nothing

'Destroy the Recordset
Set Rs = Nothing
Set Rs1 = Nothing
Exit Sub

End Sub

Good Luck
TK

 

[Jamie Collins]

Jet will not return both strings and numbers
in the same recordset so use rs for one, rs1 for
the another.

Where did you get this idea? This is clearly incorrect.

A quick demo:

Sub Test()
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
With Con

' Create Jet data source
.Open "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;" & _
"Extended Properties='Excel 8.0';"

' Create table with numeric and text columns
.Execute "" & _
"CREATE TABLE MyTable (" & _
" MyNumberCol FLOAT NULL," & _
" MyTextCol VARCHAR(255) NULL);"

' Create data
.Execute "" & _
"INSERT INTO MyTable" & _
" (MyNumberCol, MyTextCol)" & _
" VALUES (55.55, 'test');"

' Open recordset
Dim rs As Object
Set rs = .Execute("" & _
"SELECT MyNumberCol, MyTextCol" & _
" FROM MyTable;")

MsgBox "" & _
"MyNumberCol is " & _
TypeName(rs.fields("MyNumberCol").Value) & vbCrLf & _
"MyTextCol is " & _
TypeName(rs.fields("MyTextCol").Value)

rs.Close
.Close

End With

End Sub


Jamie.

--

 

[TK]

Retraction, Clarification, Whatever

Obviously you are correct in your admonishment and any
even the casual student of SQL know that all sorts of selection
and action queries can be sent to a DB.

My statement should have read: With the code I’m posting I was
unable to return both numbers and text from the same column
so I simply sent a second query to the workbook.

That being qualified, the code is fast, stable, free and answers the
specific question.

" have a closed work book I wish to extract results from into my open
workbook"


TK

 

[Jamie Collins]

My statement should have read: With the code I’m posting I was
unable to return both numbers and text from the same column
so I simply sent a second query to the workbook.

I don't understand. Your second query references a second column i.e.

strSQL = "Select * from [Sheet1$B9:B20]"
strSQL1 = "Select * from [Sheet1$A9:A10]"

so it cannot achieve your stated goal of returning 'both numbers and
text from the same column'.

If you have numbers and text in your column but you are getting null
values, Jet may be determining the data type using the 'majority type'
of the rows scanned instead of all values being coerced as 'Text'. For
details on the relevant registry settings and how you may change them
in your favor, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--

 

[TK]

..The OP stated:

“1. I know the path where the closed spreadsheet file lives
2. I know the sheet name and cell that i want to read into my
recordsetâ€

strSQL1 polls a cell, and with the proper notation will poll any cell you
like, any column you like which by definition (“1 of 1â€) would be the
majority data type.

But you know all this, so why not append or suggest ways to improve the
procedure or offer one of your own.

I merely offered a little plug and play code to get the OP who was trying to
learn to write to a closed workbook moving in the right direction.

You suggest to use my code he must adjust the Jet registry keys.
Who will do that?

“details on the relevant registry settings and how you may change them in
your favor, seeâ€

I abdicate to the readers.

TK

My statement should have read: With the code I’m posting I was
unable to return both numbers and text from the same column
so I simply sent a second query to the workbook.

I don't understand. Your second query references a second column i.e.

strSQL = "Select * from [Sheet1$B9:B20]"
strSQL1 = "Select * from [Sheet1$A9:A10]"

so it cannot achieve your stated goal of returning 'both numbers and
text from the same column'.

If you have numbers and text in your column but you are getting null
values, Jet may be determining the data type using the 'majority type'
of the rows scanned instead of all values being coerced as 'Text'. For
details on the relevant registry settings and how you may change them
in your favor, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

 

[Jamie Collins]

I merely offered a little plug and play code to get the OP who was trying to
learn to write to a closed workbook moving in the right direction.

I merely challenged some statements you made which I saw as being
incorrect.

You suggest to use my code he must adjust the Jet registry keys.

I thought *you* may have been having problems but it seems I thought
wrong.

“details on the relevant registry settings and how you may change them in
your favor, seeâ€
Who will do that?

Unless the administrator has locked them down, why not change them in
one's favor if they are causing problems? Isn't that the point of
having these values in the registry rather than hard coding them?

why not append or suggest ways to improve the
procedure or offer one of your own.

I didn't want to answer a question that has already been answered (I
assume; I haven't checked your code). But since you've laid down the
gauntlet, here's my attempt:

Sub test()
Dim vntResult As Variant
vntResult = GetCellContentsFromClosedWorkbook( _
"C:\Tempo\db.xls", "Sheet1", "A2")

If vntResult = vbEmpty Then
MsgBox "Error fetching cell contents."
Exit Sub
End If

If IsNull(vntResult) Then
MsgBox "Result is null." & vntResult
Else
MsgBox "Result=" & CStr(vntResult)
End If
End Sub

Public Function GetCellContentsFromClosedWorkbook( _
ByVal FullFilename As String, _
ByVal SheetName As String, _
ByVal CellAddress As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String
Dim strCellAddress As String
Dim lngStart As Long
Dim lngEnd As Long

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=NO'"

Const SQL As String = "" & _
"SELECT F1 FROM [" & _
"<SHEET_NAME>$" & _
"<CELL_ADDRESS>:<CELL_ADDRESS>]"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME>", FullFilename)

' Build sql statement
strSql1 = SQL

' Get first cell from address
strCellAddress = Replace(CellAddress, _
"$", vbNullString)
On Error Resume Next
lngStart = InStr(strCellAddress, "!") + 1
lngEnd = InStr(lngStart, strCellAddress, ":")
strCellAddress = Mid$(strCellAddress, _
lngStart, lngEnd - lngStart)
On Error GoTo 0

' Build sql text
strSql1 = SQL
strSql1 = Replace(strSql1, _
"<SHEET_NAME>", SheetName)
strSql1 = Replace(strSql1, _
"<CELL_ADDRESS>", strCellAddress)

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon

On Error Resume Next
.Open
Set rs = .Execute(strSql1)
GetCellContentsFromClosedWorkbook = _
rs.fields(0).Value
On Error GoTo 0

.Close
End With

End Function


Jamie.

--

 

[Jamie Collins]

Maybe usuful
http://www.rondebruin.nl/ado.htm

Ron,
I couldn't get your code to return a value from a single cell. I tried
"A2" and "A2:A2".

The code needs a little tweak, methinks (hint: HDR=NO).

Jamie

--