cancel
Showing results for 
Search instead for 
Did you mean: 

To save excel file xls from powerbuilder 11.2

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Answers (3)

Answers (3)

Former Member
0 Kudos

Try this :

if FileExists ( l_str_dir) then filedelete (l_str_dir)

iole.workbooks[1].saveAS(l_str_dir)

larrypeters
Explorer
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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()

former_member190719
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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.'