sadhana Posted February 26, 2018 Share Posted February 26, 2018 Iam trying to execute sql select query and getting the output(2d array) in excel. I can get the contents only if the table has 2 columns, if more than 2 then the excel is blank. I can get the output in message box but while trying to write into excel file the array is not copied to the excel. it is blank.it is neither showing any error. $oADODB = _SQL_Startup() If _SQL_Connect(-1,"localhost","database","username","password") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg()) Local $iRows,$iColumns;Variables to store the array data in to and the row count and the column count $iRval = _SQL_GetTable2D(-1,"SELECT * FROM db.table1;",$aData,$iRows,$iColumns) ; If $iRval = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" ) If _SQL_Close() <> $SQL_OK then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg() ) ;*****************************write to excel************************************* _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aData, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Link to comment Share on other sites More sharing options...
water Posted February 26, 2018 Share Posted February 26, 2018 Does the array look good when you run _ArrayDisplay? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sadhana Posted February 27, 2018 Author Share Posted February 27, 2018 Yes , Array displays all rows and columns. But in excel it is not getting updated. it is blank. Link to comment Share on other sites More sharing options...
Andreik Posted February 27, 2018 Share Posted February 27, 2018 #include <SQL.au3> #include <Array.au3> #include <Excel.au3> _AutoItObject_Startup() $oSQL = SQL() With $oSQL .Server = 'localhost' .Database = 'Test' .TrustedConnection = True .Connect() EndWith $oQuery = $oSQL.Query('SELECT * FROM Types') $aData = $oQuery.Data $oExcel = _Excel_Open(False) $oBook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oBook,Default,$aData) _Excel_BookSaveAs($oBook,@ScriptDir & '\test.xlsx',$xlOpenXMLWorkbook) _Excel_Close($oExcel) SQL.au3 is my personal OOP UDF for SQL Server expandcollapse popup#include-once #include <AutoItObject.au3> Global $TypeEnum[40][2] = [['Array',0x2000], ['adBigInt',20], ['Binary',128], ['Boolean',11], ['BSTR',8], ['Chapter',136], ['Char',129], _ ['Currency',6], ['Date',7], ['DBDate',133], ['DBTime',134], ['DBTimeStamp',135], ['Decimal',14], ['Double',5], ['Empty',0], ['Error',10], ['FileTime',64], _ ['GUID',72], ['IDispatch',9], ['Integer',3], ['IUnknown',13], ['LongVarBinary',205], ['LongVarChar',201], ['LongVarWChar',203], ['Numeric',131], ['PropVariant',138], _ ['Single',4], ['SmallInt',2], ['TinyInt',16], ['UnsignedBigInt',21], ['UnsignedInt',19], ['UnsignedSmallInt',18], ['UnsignedTinyInt',17], ['UserDefined',132], _ ['VarBinary',204], ['VarChar',200], ['Variant',12], ['VarNumeric',139], ['VarWChar',202], ['WChar',130]] Func SQL() Local $oClass = _AutoItObject_Class() With $oClass .AddMethod("Connect", "__sql_connect") .AddMethod("Query" , "__sql_query") .AddMethod("Execute" , "__sql_execute") .AddProperty("Provider", $ELSCOPE_PUBLIC, 'SQLNCLI11') .AddProperty("Server", $ELSCOPE_PUBLIC, Null) .AddProperty("Database", $ELSCOPE_PUBLIC, Null) .AddProperty("Username", $ELSCOPE_PUBLIC, Null) .AddProperty("Password", $ELSCOPE_PUBLIC, Null) .AddProperty("TrustedConnection", $ELSCOPE_PUBLIC, False) .AddProperty("ADO", $ELSCOPE_PRIVATE, Null) EndWith Return $oClass.Object EndFunc Func Query($oConnection) Local $oClass = _AutoItObject_Class() With $oClass .AddMethod("Query", "__query_execute") .AddProperty("Data", $ELSCOPE_READONLY, 0) .AddProperty("Struct", $ELSCOPE_READONLY, 0) .AddProperty("Columns", $ELSCOPE_READONLY, 0) .AddProperty("Rows", $ELSCOPE_READONLY, 0) .AddProperty("ADO", $ELSCOPE_PRIVATE, $oConnection) EndWith Return $oClass.Object EndFunc Func __sql_connect($oSelf,$Provider=Null,$Server=Null,$Database=Null,$Username=Null,$Password=Null,$TrustedConnection=Null) #forceref $oSelf If $Provider <> Null Then $oSelf.Provider = $Provider If $Server <> Null Then $oSelf.Server = $Server If $Database <> Null Then $oSelf.Database = $Database If $Username <> Null Then $oSelf.Username = $Username If $Password <> Null Then $oSelf.Password = $Password If $TrustedConnection <> Null Then $oSelf.TrustedConnection = $TrustedConnection If $oSelf.ADO = Null Then $oSelf.ADO = ObjCreate("ADODB.Connection") Local $oConnection = $oSelf.ADO $oConnection.CursorLocation = 3 If $oSelf.TrustedConnection Then $oConnection.Open('Provider=' & $oSelf.Provider & ';Server=' & $oSelf.Server & ';Database=' & $oSelf.Database & ';Trusted_Connection=yes;') Else $oConnection.Open('Provider=' & $oSelf.Provider & ';Server=' & $oSelf.Server & ';Database=' & $oSelf.Database & ';Uid=' & $oSelf.Username & ';Pwd=' & $oSelf.Password) EndIf EndFunc Func __sql_query($oSelf,$sQuery,$fStruct=False) #forceref $oSelf Local $oQuery = Query($oSelf.ADO) $oQuery.Query($sQuery,$fStruct) Return $oQuery EndFunc Func __sql_execute($oSelf,$sCommand) #forceref $oSelf $oSelf.ADO.Execute($sCommand) EndFunc Func __query_execute($oSelf,$sQuery,$fStruct=False) #forceref $oSelf Local $oRecord = $oSelf.ADO.Execute($sQuery) Local $oFields = $oRecord.Fields $oSelf.Rows = $oRecord.RecordCount $oSelf.Columns = $oFields.Count If $fStruct Then Local $aStruct[$oSelf.Columns][5] For $Index = 0 To $oSelf.Columns-1 $aStruct[$Index][0] = $oFields.Item($Index).Name $aStruct[$Index][1] = __adType($oFields.Item($Index).Type) $aStruct[$Index][2] = $oFields.Item($Index).DefinedSize $aStruct[$Index][3] = $oFields.Item($Index).ActualSize $aStruct[$Index][4] = $oFields.Item($Index).Precision Next $oSelf.Struct = $aStruct EndIf Local $aData[$oSelf.Rows][$oSelf.Columns] While Not $oRecord.EOF For $Column = 0 To $oSelf.Columns-1 $aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column).Value Next $oRecord.MoveNext WEnd $oSelf.Data = $aData EndFunc Func __adType($iType) Local $sType If $iType >= 0x2000 Then $sType = 'Array/' $iType = BitXOR($iType,0x2000) EndIf For $Index = 0 To UBound($TypeEnum) If $iType = $TypeEnum[$Index][1] Then Return $sType & $TypeEnum[$Index][0] Next EndFunc and you will also need AutoItObject.au3 included in AutoItObject UDF. But you can also try just the part where array is written in excel file. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
sadhana Posted February 27, 2018 Author Share Posted February 27, 2018 i tried only writing the array into excel part, its again a blank excel. I also tried with your sql udf . it is showing the error "D:\658610\SQL&Outlook\SQL.au3" (90) : ==> The requested action with this object has failed.: $aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column).Value $aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column)^ ERROR Link to comment Share on other sites More sharing options...
Andreik Posted February 27, 2018 Share Posted February 27, 2018 What version of Excel do you have installed? Use the appropriate export type and file extension. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
water Posted February 27, 2018 Share Posted February 27, 2018 Could you please set the _Excel_RangeWrite parameter $bForceFunc to True and try again? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Mercy Posted March 7, 2018 Share Posted March 7, 2018 @water yes, now it is working. thanks! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now