Sign in to follow this  
Followers 0
mdthanh

Export data from Recordset to Excel

2 posts in this topic

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

Share this post


Link to post
Share on other sites



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,

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0