Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow
Support > Technical Documents > Document Types > Technote > Copying Datawindow data to Microsoft Excel using O...

Copying Datawindow data to Microsoft Excel using OLE Automation

OLE automation in PowerBuilder can copy DataWindow data to Excel. This is a sample application that will copy all columns and rows to MS Excel.
RSS Feed

The sample copies Datawindow Row 1 ,Column 1 to MS Excel Row 1 , Column 1 etc...  

The script can be modified to copy only the desired columns/rows.  A template xls file could be opened and saved to a different xls filename, preserving the template.   The sample pbl provided uses the EAS Demo DB's Employee table.

The following script code is located in the "Copy DW Data to Excel using OLE Automation"
button , on the only window in the pbl. The example pbl is currently setup to open the file "c:\file1.xls"

Modify the path/filename or create file1.xls in the c:\ directory. The file can be an empty xls file.   Uncomment the Save or SaveAs lines accordingly.

long numcols , numrows , c, r
OLEObject xlapp , xlsub
int ret

// Set the # of columns and rows to process
// Currently Set to copy the entire DW

numcols = long(dw_1.Object.DataWindow.Column.Count)
numrows = dw_1.RowCount()

// Create the oleobject variable xlapp
xlApp = Create OLEObject

// Connect to Excel and check the return code
ret = xlApp.ConnectToNewObject( "Excel.Sheet" )
if ret < 0 then
MessageBox("Connect to Excel Failed !",string(ret))
end if

// Open a particular Excel file
xlApp.Application.Workbooks.Open("c:\file1.xls") //,false,true
// Make Excel visible
xlApp.Application.Visible = true

// Resolve the Excel reference once
// This technique shortens the script and improves performance
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[1]

// Loop thru the Datawindow and Excel sheet
// The for/next loop copies all rows for each column
For c = 1 to numcols
For r = 1 to numrows
xlsub.cells[r,c] =[r,c]


// Save opened file

// SaveAs a different filename

// clean up
Destroy xlapp


Last Revised: Mar 24, 2003
Product: PowerBuilder
Hardware Platform: Win NT, Windows x86
Technical Topics: DataWindows
Business or Technical: Technical
Content Id: 47867
Infotype: Technote

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics