on 07-23-2013 11:43 AM
Anybody can advise me to save an excel (.xls 2003 format) from PowerBuilder , the excel application not be visible to the user . Following is my code. I want to save that file ( if exist , it should be overwritten) without manual involvement .
If sle_subject.text= "Purchase Order" Then
OleObject lole_excel
lole_excel = CREATE OLEObject
li_rtn = lole_excel.ConnectToNewObject("excel.application")
IF li_rtn <> 0 THEN
DESTROY lole_Excel
END IF
End if
If sle_subject.text= "Purchase Order" and li_rtn = 0 Then
l_str_dir = GetCurrentDirectory( )+'\'+ls_filename
li_nextrow = l_lng_rowcnt + 1
li_lastrow = li_nextrow + 1
lole_excel.visible = TRUE
lole_excel.WorkBooks.Open(l_str_dir)
lole_excel.Application.Visible = TRUE
IF li_rtn = 0 THEN
lole_excel.visible = TRUE
lole_excel.Application.DisplayAlerts = False
lole_excel.WorkBooks.Open(l_str_dir)
lole_excel.Application.Visible = TRUE
lole_excel.application.workbooks(1). worksheets(1).Columns(1).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(5).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(7).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(8).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(9).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(10).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(11).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Columns(9).NumberFormat = "#,##0"
lole_excel.application.workbooks(1). worksheets(1).Columns(10).NumberFormat = "£#,##0.00"
lole_excel.application.workbooks(1). worksheets(1).Columns(11).NumberFormat = "£#,##0.00"
lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").Select
lole_excel.application.workbooks(1). worksheets(1).Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3
lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").Font.Bold = True
lole_excel.application.workbooks(1). worksheets(1).Columns("A:L").EntireColumn.AutoFit
lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").HorizontalAlignment = -4108
li_nextrow =lole_excel.application.workbooks(1).worksheets(1).usedrange.rows.count
li_lastrow = li_nextrow+1
lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Formula = "TOTAL"
lole_excel.application.workbooks(1). worksheets(1).Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Formula = "=SUM(K1:K"+string(li_nextrow)+")"
lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Font.ColorIndex = 3
lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).HorizontalAlignment = -4108
lole_excel.application.workbooks(1). worksheets(1).Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Font.ColorIndex = 3
END IF
DESTROY lole_excel
Else
IF ShellExecute( handle(lw_null), ls_cmd, ls_filename, ls_null, ls_null, SW_NORMAL ) < 33 THEN
RETURN 0
END IF
End if
After doing some format on the excel I want , that to be saved again without user involvement . Please any idea will be appreciated . I am using powerbuilder 11.2
Pol
It looks like you just need to call the workbook.save() function. A quick google yielded this example...
http://stackoverflow.com/questions/11223315/write-and-read-from-excel-using-powerbuilder
hth,
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
And if you want Excel to be invisible, then why are you setting
lole_excel.Application.Visible = TRUE
Here is some code I use:
ioo_excel = CREATE OleObject
li_rc = ioo_excel.ConnectToNewObject("Excel.Application")
choose case li_rc
case 0
ioo_excel.workbooks.Add()
case ...
end choose
...
ioo_excel.activeworkbook.saveas(ls_filesuggest)
ioo_excel.activeworkbook.close()
ioo_excel.Application.quit // Ensure Excel is closed properly
ioo_excel.DisconnectObject()
DESTROY ioo_excel
Thanks for the reply. When I apply that code the 'Null object reference is coming' in line lole_workbook.save() . The following is my code. Please anybody help will appreciate OleObject lole_excel,lole_workbook If sle_subject.text= "Purchase Order" Then lole_excel = CREATE OLEObject li_rtn = lole_excel.ConnectToNewObject("excel.application") IF li_rtn <> 0 THEN DESTROY lole_Excel END IF End if If sle_subject.text= "Purchase Order" and li_rtn = 0 Then l_str_dir = GetCurrentDirectory( )+'\'+ls_filename li_nextrow = l_lng_rowcnt + 1 li_lastrow = li_nextrow + 1 lole_excel.visible = TRUE lole_excel.WorkBooks.Open(l_str_dir) lole_excel.Application.Visible = TRUE IF li_rtn = 0 THEN lole_excel.visible = TRUE lole_excel.Application.DisplayAlerts = False lole_excel.WorkBooks.Open(l_str_dir) lole_workbook = lole_excel.application.workbooks(1) lole_excel.Application.Visible = TRUE lole_excel.application.workbooks(1). worksheets(1).Columns(1).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(5).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(7).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(8).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(9).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(10).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(11).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Columns(9).NumberFormat = "#,##0" lole_excel.application.workbooks(1). worksheets(1).Columns(10).NumberFormat = "£#,##0.00" lole_excel.application.workbooks(1). worksheets(1).Columns(11).NumberFormat = "£#,##0.00" lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").Select lole_excel.application.workbooks(1). worksheets(1).Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3 lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").Font.Bold = True lole_excel.application.workbooks(1). worksheets(1).Columns("A:L").EntireColumn.AutoFit lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").HorizontalAlignment = -4108 li_nextrow =lole_excel.application.workbooks(1).worksheets(1).usedrange.rows.count li_lastrow = li_nextrow+1 lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Formula = "TOTAL" lole_excel.application.workbooks(1). worksheets(1).Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Formula = "=SUM(K1:K"+string(li_nextrow)+")" lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Font.ColorIndex = 3 lole_excel.application.workbooks(1). worksheets(1).Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).HorizontalAlignment = -4108 lole_excel.application.workbooks(1). worksheets(1).Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Font.ColorIndex = 3 END IF lole_workbook.save() // Here is the error message is coming DESTROY lole_excel Else IF ShellExecute( handle(lw_null), ls_cmd, ls_filename, ls_null, ls_null, SW_NORMAL ) < 33 THEN RETURN 0 END IF End if
Try this :
if FileExists ( l_str_dir) then filedelete (l_str_dir)
iole.workbooks[1].saveAS(l_str_dir)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Pol,
Some code I have used in the past using OLE.
See if this helps...
// A datastore containing data to be inserted into Excel
// May have 1 to many (x) columns
ll_rows = lds_mydata.rowcount()
// Excel datastore has 1 to many (y) columns - for this example - 8 columns
// Each column will occupy a corresponding column in the Excel spreadsheet
lds_excel = create datastore
lds_excel.dataobject = "dw_excel"
for ll_row = 1 to ll_rows
ll_xcl = lds_excel.insertrow(0)
lds_excel.setitem(ll_xcl, "sscolumn1", lds_mydata.getitemnumber(ll_row, "sscolumna")))
lds_excel.setitem(ll_xcl, "sscolumn2", lds_mydata.getitemnumber(ll_row, "sscolumnb")))
lds_excel.setitem(ll_xcl, "sscolumn3", lds_mydata.getitemnumber(ll_row, "sscolumnd")))
lds_excel.setitem(ll_xcl, "sscolumn4", lds_mydata.getitemdate(ll_row, "sscolumnf")))
lds_excel.setitem(ll_xcl, "sscolumn5", lds_mydata.getitemstring(ll_row, "sscolumng")))
lds_excel.setitem(ll_xcl, "sscolumn6", lds_mydata.getitemstring(ll_row, "sscolumnh")))
lds_excel.setitem(ll_xcl, "sscolumn7", lds_mydata.getitemdate(ll_row, "sscolumnh")))
lds_excel.setitem(ll_xcl, "sscolumn8", lds_mydata.getitemstring(ll_row, "sscolumni")))
next
// Connect to Excel via OLE
xlApp = create OLEObject
xlApp.ConnectToNewObject("Excel.Application")
// Now there are two options
// Option 1. saves the excel file and opens it later
// Save the Excel datastore as an Excel file
lds_excel.SaveAs("c:\somwhere\somefile.xls", Excel5!, False)
// Now to open an Excel worksheet...
xlApp.Application.Workbooks.Open("c:\somwhere\somefile.xls")
// Option 2. does not create a file but works in memory
xlApp.Application.Workbooks.Add()
ll_maxrows = 10000
ll_rows = lds_excel.rowcount()
if ll_rows < ll_maxrows then
ls_data = lds_excel.Object.DataWindow.Data
ClipBoard(ls_data)
xlApp.ActiveWorkbook.Sheets(1).Paste()
else
ll_row = 1
do while ll_maxrows > 0
lds_temp = create datastore
lds_temp.dataobject = lds_excel.dataobject
lds_excel.rowscopy(ll_row, ll_row + ll_maxrows - 1, primary!, lds_temp, 1, primary!)
ls_data = lds_temp.Object.DataWindow.Data
ClipBoard(ls_data)
xlApp.ActiveWorkbook.Sheets(1).Range("A" + string(ll_row)).Select
xlApp.ActiveWorkbook.Sheets(1).Paste()
destroy lds_temp
ll_row += ll_maxrows
ll_rows = ll_rows - ll_maxrows
if ll_rows < ll_maxrows then
ll_maxrows = ll_rows
end if
loop
end if
// Get a handle to the worksheet[1]
xlSub = xlApp.Application.ActiveWorkbook.Worksheets[1]
// Insert Lines for Header & Miscellaneous Details (16 lines above columnar data)
xlSub.Range("A1:H16").Select
xlApp.Application.Selection.EntireRow.Insert
// Some Data 1 - demonstrates how to merge & embold cells/ranges
// -------------------------------------------------------------------
ls_range = "A4:B4"
xlSub.Range(ls_range).Select
xlApp.Selection.Merge()
xlSub.Range(ls_range).Font.Bold = TRUE
xlSub.cells[4, 1] = "Some Data 1"
// Some Data 2
// -------------------------------------------------------------------
ls_range = "A5:B5"
xlSub.Range(ls_range).Select
xlApp.Selection.Merge()
xlSub.cells[5, 1] = "Some Data 2"
...
// Some Data 12
// -------------------------------------------------------------------
ls_range = "G11:I11"
xlSub.Range(ls_range).Select
xlApp.Selection.Merge()
xlSub.cells[11, 7] = "Some Data 12:"
// Headings for columnar data
// -------------------------------------------------------------------
xlSub.cells[15, 1] = "Age"
xlSub.cells[15, 2] = "Height"
xlSub.cells[15, 3] = "Weight"
xlSub.cells[15, 4] = "Dat of Birth"
xlSub.cells[15, 5] = "Place of Birth"
xlSub.cells[15, 6] = "Country"
xlSub.cells[15, 7] = "Date of Signing"
xlSub.cells[15, 8] = "Name"
// Underline column labels
// -------------------------------------------------------------------
CONSTANT INTEGER xlEdgeBottom = 9
CONSTANT INTEGER xlContinuous = 1
CONSTANT INTEGER xlMedium = -4138
CONSTANT INTEGER xlThick = 4
CONSTANT INTEGER xlThin = 2
// Use Excel with Visual Basic to get values
xlSub.Range("A15:H15").Select
xlApp.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
xlApp.Selection.Borders(xlEdgeBottom).Weight = xlThin
// Format sheet
ls_range = ""A1:H" + STRING(lds_excel.RowCount() + 17)
xlSub.Range(ls_range).Select
aole_xl.Selection.Font.Size = 8
aole_xl.Selection.Font.Name = "Arial"
xlSub.Columns("A:H").EntireColumn.AutoFit
// Change number format for a column
xlSub.Range("A16:C" + STRING(lds_excel.RowCount())).Select
xlApp.Selection.NumberFormat = "#,##0.000"
// Save all changes
xlSub.Range("A1:A1").Select
xlApp.Application.DisplayAlerts = FALSE
xlApp.Save()
// Disconnect session of Excel
xlSub.DisConnectObject()
destroy xlSub
destroy lds_excel
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi POLACHAN,
I notice two succeeding End If above. You create the oleobject inside that If statement. If that condition is false, the oleobject is not created, hence, it would be null.
you can use something like:
oleobject ole_wb, ole_ws, ole_excel
Integer li_rc
li_rc = ole_excel.ConnectToNewObject('excel.application')
If li_rc <> 0 Then
messagebox('','Unable to connect to Microsoft Excel, RC= ' + String(li_rc) + '.')
Return -1
End If
ole_excel.workbooks.open("filename.xlsx")
ole_wb = ole_excel.application.workbooks(1)
ole_ws = ole_wb.worksheets(1)
Instead of:
lole_excel.application.workbooks(1). worksheets(1).Range("A1:K1").Select lole_excel.application.workbooks(1). worksheets(1).Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3
you can just use:
ole_ws.Range("A1:K1").Select
ole_ws.Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3
for saving without user intervention, use:
ole_wb.save()
To display the open workbook: - would be good to have this at the bottom part, after you're done setting the values.
ole_excel.visible=true
for exiting the application and disconnecting:
ole_excel.Application.Quit
ole_excel.disconnectobject()
destroy ole_excel
HTH,
Neil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Neil , When I apply save command
ole_wb.save() ' the error calling external object function save not found' . Please find my code below and can you advise me.
ole_excel.visible = TRUE
ole_excel.Application.DisplayAlerts = False
ole_excel.WorkBooks.Open(l_str_dir)
ole_workbook = ole_excel.application.workbooks(1)
ole_worksheet = ole_workbook.worksheets(1)
ole_excel.Application.Visible = True
ole_worksheet.Columns(1).HorizontalAlignment = -4108
ole_worksheet.Columns(5).HorizontalAlignment = -4108
ole_worksheet.Columns(7).HorizontalAlignment = -4108
ole_worksheet.Columns(8).HorizontalAlignment = -4108
ole_worksheet.Columns(9).HorizontalAlignment = -4108
ole_worksheet.Columns(10).HorizontalAlignment = -4108
ole_worksheet.Columns(11).HorizontalAlignment = -4108
ole_worksheet.Columns(9).NumberFormat = "#,##0"
ole_worksheet.Columns(10).NumberFormat = "#,##0"
ole_worksheet.Columns(11).NumberFormat = "£#,##0.00"
ole_worksheet.Range("A1:K1").Select
ole_worksheet.Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3
ole_worksheet.Range("A1:K1").Font.Bold = True
ole_worksheet.Columns("A:L").EntireColumn.AutoFit
ole_worksheet.Range("A1:K1").HorizontalAlignment = -4108
li_nextrow =ole_worksheet.usedrange.rows.count
li_lastrow = li_nextrow+1
ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Formula = "TOTAL"
ole_worksheet.Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Formula = "=SUM(K1:K"+string(li_nextrow)+")"
ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Font.ColorIndex = 3
ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).HorizontalAlignment = -4108
ole_worksheet.Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Font.ColorIndex = 3
ole_workbook.save() // Here is the error 'External object function save not found.
DESTROY ole_excel
What version of MS Office you have?
Could you post the whole script from creating the oleobject and ConnectToNewObject.
Is the value of l_str_dir pointing to an existing excel file?
Move this statement after the save()
ole_excel.visible = TRUE
Remove this statement.
ole_excel.Application.Visible = True
Before destroying the oleobject, disconnect it first:
ole_excel.DisconnectObject()
A few things:
Are you sure this worked:
ole_excel.WorkBooks.Open(l_str_dir)
If it didn't, then the worksheet you're working with doesn't have a name yet, and without a name the Save function wouldn't work. You would need to use SaveAs instead.
Are you actually starting from an existing file? If not, just do this instead:
ole_excel.workbooks.Add()
You would then have to set the filename through SaveAs.
I have a very strong feeling that the file your script is opening has already been opened. Before running that script, make sure you close that file you are opening. Also, check on task manager if there are not any hanging thread of excel running, exit/end task them if there are.
Since you did not issue the ole_excel.Application.Quit, the excel application is open when you run it the first time and then you run it again while the file is still open. In this case, the save method will fail with the error 'External object function save not found.'
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.