ADODB Connection to Excel Object

[litlmike]

I have some code from another script that the community helped create. No I would like to modify this in a new script to work under a new circumstance. I would like to create an ADODB connection to a Excel object that has just been created. In the original code, the conneciton was created with a Excel file that was previously saved and closed. In this script I would like to create a visible Excel File, then create the ADODB connection to it. But, I have no idea how to do that. Below is the code that I am working with. The first section is the Excel object, the second section is the ADODB connection and Record Set query.

My guess is that this line has to change:

$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="&$s_Filename&";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";")

Thanks in Advance!

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;   Create Excel File;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;~  Create an Excel File to Print to
   $oExcel = ObjCreate("Excel.Application")               ; Create an Excel Object
   $oExcel.Visible = 1                                     ; Let Excel show itself
   $oExcel.WorkBooks.Add

~ ;Create Connections
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001;  =&H0001
    Global $s_Filename= FileGetShortName(@ScriptDir & "\Temp.xls")
    Global $s_Tablename = "[Sheet1$]"; 
    $s_Url = "http://www.google.com"
    $oIE = _IECreate ("http://www.google.com")
    $i = 1
    $j = 1

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;   Create Excel File;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;~  Create an Excel File to Print to
   $oExcel = ObjCreate("Excel.Application")               ; Create an Excel Object
   $oExcel.Visible = 1                                     ; Let Excel show itself
   $oExcel.WorkBooks.Add   
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;   Create ADODB Connection;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

    $query = "SELECT * FROM " & $s_Tablename 
    $objConnection = ObjCreate("ADODB.Connection")
    $objRecordSet = ObjCreate("ADODB.Recordset")
    $objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        
;~ ;Grabes the data from Excel, based on the Criteria for the Query
    $objRecordSet.Open ( $query, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

[lod3n]

A "visible" Excel file? Meaning you want to run SQL statements against it to update it and show the results in real time? I don't think you can do that. When Excel has a file open, it's locked.

[litlmike]

A "visible" Excel file? Meaning you want to run SQL statements against it to update it and show the results in real time? I don't think you can do that. When Excel has a file open, it's locked.

Yes that is what I would like to do. Darn it! Its locked? Are you positive?

[1905russell]

Yes that is what I would like to do. Darn it! Its locked? Are you positive?

Maybe as a workaround you could update values on a visible sheet that is linked to the closed one that you (I think) are SQL updating to? Edited December 9, 2006 by 1905russell

[randallc]

Maybe as a workaround you could update values on a visible sheet that is linked to the closed one that you (I think) are SQL updating to?

or.

1. Go back to locoDarwin's offer of SQL in Excel.

2. If you are only reading, make a copy of the open file and SQL read that instead?

Best, randall

[1905russell]

or.

1. Go back to locoDarwin's offer of SQL in Excel.

2. If you are only reading, make a copy of the open file and SQL read that instead?

Best, randall

1)Not sure I understand - LocoDarwins offer? I guess you mean non-ADODB connection. This is an alternative more than a workaround - no?

2) How does this work? I think litlmike wants to see live updating? Im imagining a view-only-worksheet (created with PasteLink) and a GUI visible. The GUI updates via ADODB a closed db-only-worksheet. Then in the v-o-worksheet - Edit> Links> Update Values.

I think you are saying update the v-o-worksheet without Ado then make a copy called db-worksheet and connect that with Ado? I guess I dont understand what litlmike wants or why he is suddenly upset that the Ado worksheet is locked (I hit this problem the very first time I tested your little script that introduced me to Ado). Thanks.

[ptrex]

@

This is a working example of retrieving data from Excel via ADADB.

expandcollapse popup

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001;  =&H0001 VBScript Constant
Global $s_Filename=FileGetShortName("C:\Book1.xls"); 
Global $s_Tablename = "[Sheet1$]"; 

; Initialize COM error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")

$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")

$objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 3 Asc" , _
                $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText); Where CustomerCode = '004301' Order by CustomerCode Asc;
                                                                             ; "Select * FROM"& $s_Tablename & "Order by 3 Asc"     
Do 
; Important difference between VBS and AU3 format : in AU3 it is $objRecordSet.Fields.Item("Plant").value (including the ".Value" !!)
; This is a bug in AU3 see buglist
; msgbox (0,"Test",$objRecordSet.Fields.Item("Plant").value &" | "& $objRecordSet.Fields.Item("CustomerCode").value &" | "& _
;                $objRecordSet.Fields.Item("Item").value &" | "& $objRecordSet.Fields.Item("ItemDesc").value)
    
; Or you can use the Field INDEX nr. to load the data :
     ConsoleWrite ($objRecordSet.Fields(1).value &" | "& $objRecordSet.Fields(2).value &" | "& _
                    $objRecordSet.Fields(4).value &" | "& $objRecordSet.Fields(5).value&@CR)
        $objRecordSet.MoveNext()
Until $objRecordSet.EOF()

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc

Enjoy,

ptrex

[randallc]

Hi @1905Russell;

I agree with your comments; I am not sure what @litlmike wants, so cannot take it further; I agree your idea looks good.

1. "non-ADODB"; if you mean to do the SQL query in Excel, without ADOR, then yes; it is possible to run an excel macro to do the query while it is open.

2. I imagine the issue is just doing the query while open, rather than requiring updating; but I may be wrong?

@ptrex; I think yours still won't work if the file is open in Excel, which seems to be the current question?

Best, Randall

Edited December 10, 2006 by randallc

[ptrex]

@all

This shows the different ways of transfering data to Excel.

http://support.microsoft.com/kb/247412

regards

ptrex

[1905russell]

@ptrex; I think yours still won't work if the file is open in Excel, which seems to be the current question?

Best, Randall

Thanks for all the valuable info but randallc is right - the current issue is the inability for Ado to connect to an open xls. Edited December 11, 2006 by 1905russell

[litlmike]

Thanks for everyone's input. I decided to use a workaround in this instance, so I don't have to use the ADODB connection to read the Excel File. What I decided to do was to create an array simultaneously, as the Excel Object is created, and the cells are given values. That way I only have to query an array, instead of an open Excel file.

However, if anyone knows how to read the values in an Excel object that was just created, please let me know. Whether it is using a ADODB connection or not. For instance:

1) Create Excel Object and Worksheet

2) Assign Values to Cells

3) Have another function read those cells with a Value

4) Do something else with those values.

Thanks.

[randallc]

Hi,

3) Have another function read those cells with a Value

Of course any of the excel COM UDFs wil do it or have code to show you how to read the array of values in an open sheet into an array in Autoit.

eg (link in my sig for examples ExcelCOM.au3)

;_XLArrayExample2Single3.au3
#include"ExcelCOM.au3"
;FIRST Create sheet;
$FilePath=@ScriptDir&"\Blank50.xls"
$DataString="12,7,6,9,23,45,3,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"3,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"
$DataString=StringReplace($DataString,",",@TAB)
$XLRange=_XLpaste($FilePath,1,"Z",11,$DataString,1)
;NOW read it to array;
$XLArray=_XLArrayRead($FilePath,1,$XLRange)
_Array2dDisplay($XLArray, "Array as read",0)
_XLClose($FilePath,1)

Best, randall