Re: How to SELECT UNION data from two Excel files???

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for helping, Paul. But, when I run it, I get "ADODB.Recordset: The
connection can not be used to perform this operation. It is either closed or
invalid in this context."

If I remove the adOpenStatic and adLockReadOnly params, it seems to go
through but, RecordCount returns -1, although records do exist.

If I do it w/o the UNION, i.e.
oRec.Open "Select project_No from [AllPN2$]", oDb, adOpenStatic,
adLockReadOnly
I get the same symptoms.

Code follows. Help appreciated...

set oDb = CreateObject("ADODB.Connection")
set oRec = CreateObject("ADODB.Recordset")

oDb.Open _
"Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=z:\data\std\AllPN2.xls" & _
";Extended Properties=Excel 8.0"

oRec.Open "Select project_No from [AllPN2$]" & _
"UNION Select project_No from [Excel
8.0;DATABASE=z:\data\std\Wind2.xls].[Sheet1$]" _
, oDb, adOpenStatic, adLockReadOnly

MsgBox oRec.RecordCount


"Paul Clement" <UseAdddressAtEndofMessage@xxxxxxxxxxxxxx> wrote in message
news:7605s191q95c8brvdob3l6tihce31n8d80@xxxxxxxxxx
> On Thu, 5 Jan 2006 15:14:33 -1000, "cbv" <not@xxxxxxxx> wrote:
>
> ¤ Hello,
> ¤
> ¤ Using vbscript and ADO, I would like to know how to...
> ¤ SELECT field1, field2 FROM file1.xls UNION SELECT field1, field2
FROM
> ¤ file2.xls
> ¤
> ¤ Please give example connection strings and SELECT statements.
>
> Dim cnn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
>
> cnn.Open _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=e:\My Documents\Book20.xls;Extended Properties=Excel 8.0"
>
> rs.Open "Select * from [Table4$] UNION Select * from [Excel
8.0;DATABASE=e:\My
> Documents\Book10.xls;HDR=No;IMEX=1].[Sheet7$]", cnn, adOpenStatic,
adLockReadOnly
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)


.

Quantcast