Using OLE automation in PowerBuilder 6.x, 7.x and 8.x you can copy data from a datawindow to an Excel 8, Excel 97 or Excel 2000 spreadsheet much faster than with previous versions of PB. There is also a sample application available for download that will copy all columns and rows to a MS Excel spreadsheet.
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.
Uncomment the Save or SaveAs lines accordingly.
The sample pbl provided uses the EAS DEMO DB's Employee table.
47867.zip is an sample PBL for techdoc 47867 "Copying Datawindow data to Microsoft Excel using OLE Automation"
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.
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))
return
end if
// Open a particular Excel file
xlApp.Application.Workbooks.Open("c:\file1.xls") //,false,true
// Make Excel visible
xlA
