Export data from Recordset to Excel

[mdthanh]

Hi,

I have looking for the code to export data from MSSQL to excel but could not and after spend some hours I completed the code to do it as below:

#include <sqlconnect.au3>

#include <GUIConstantsEx.au3>

#include <Excel.au3>

$oConnectSQL = _SQLConnect('MSSQLServer', 'MSSSQLDBName', 1, 'UserName', 'Password')

; GUI

GuiCreate("Sample GUI", 400, 400)

; BUTTON

$varSalesTeamCode = GuiCtrlCreateButton("Export", 150, 65, 100, 30)

;Show window/Make the window visible

GUISetState(@SW_SHOW)

While 1

;After every loop check if the user clicked something in the GUI window

$msg = GUIGetMsg()

Select

Case $msg = $GUI_EVENT_CLOSE

;Destroy the GUI including the controls

_SQLDisconnect($oConnectSQL)

GUIDelete()

;Exit the script

Exit

;Check sales team amount

Case $msg = $varSalesTeamCode

$oExeResult = _SQLQuery($oConnectSQL, "Your Query Here")

if not IsObj($oExeResult) then

Msgbox(0,"Error","$oExeResult is not an Object.")

else

$oExcel = ObjCreate("Excel.Application") ; Create an Excel Object

$oExcel.Visible = 1 ; Let Excel show itself

$oExcel.WorkBooks.Add ; Add a new workbook

$oExcel.ActiveWorkBook.ActiveSheet.Cells(1,1).CopyFromRecordset ($oExeResult) ; Fill a cell

$oExcel.ActiveWorkBook.Saved = 1 ; Simulate a save of the Workbook

MsgBox(0, "Exiting", "Export complete, Press OK to Save File and Exit")

endif

EndSelect

WEnd

Hope it could help someone.

Cheers,

sqlconnect.au3

[mdthanh]

Hi All,

I forgot one thing:

$oExcel.ActiveWorkBook.ActiveSheet.Cells(1,1).CopyFromRecordset ($oExeResult)

Just work with Excel 2000 and later.

For excel 97 or earlier, you need to do another way that could transfer all data to array before copy to excel.

Sorry,