0

There is a requirement to save an excel sheet as a pdf file programmatically through powerbuilder (Powerbuilder 12.5.1).

I run the code below; however, I am not getting the right results. Please let me know if I should do something different.

 OLEObject  ole_excel;
 ole_excel = create OLEObject;

 IF ( ole_excel.ConnectToObject(ls_DocPath) = 0 ) THEN
      ole_excel.application.activeworkbook.SaveAs(ls_DocPath,17);
      ole_excel.application.activeworkbook.ExportAsFixedFormat(0,ls_DocPath);
 END IF;
 ....... (Parsing values from excel)
 DESTROY ole_excel;

I have searched through this community and others for a solution but no luck so far. I tried using two different commands that I found during this search. Both of them return a null object reference error. It would be great if someone can point me in the right direction.

Mikel Urkia
  • 2,087
  • 1
  • 23
  • 40
Vijay Raghavan
  • 53
  • 11

2 Answers2

0

workBook.saveAs() documentation says that saveAs() has the following parameters:

SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

we need the two first params:

FileName - full path with filename and extension, for instance: c:\myfolder\file.pdf

FileFormat - predefined constant, that represents the target file format. According to google (MS does not list pdf format constant for XLFileFormat), FileFormat for pdf is equal to 57

so, try to use the following call:

ole_excel.application.activeworkbook.SaveAs(ls_DocPath, 57);
  • Thank you for the reply Rafael. Still it does not work after using the code 57. I am still receiving the null object reference error on that particular line. Do I need a special dll or something for performing this SaveAs? – Vijay Raghavan Mar 26 '15 at 10:35
  • According to [MSDN article](https://msdn.microsoft.com/en-us/library/bb407651(v=office.12).aspx) (see section "4") it is requred to have "Save as PDF add-in" installed. Otherwise `Workbook.ExportAsFixedFormat` generates an exception. Hope this helps. –  Mar 26 '15 at 10:50
  • here is the link for the add-in: http://www.microsoft.com/en-us/download/details.aspx?id=7 –  Mar 26 '15 at 10:52
  • I have installed this add in. Still does not work. Thanks for the suggestion though. – Vijay Raghavan Mar 26 '15 at 12:43
  • Consider printing your excel file as PDF. Check this link: http://stackoverflow.com/questions/3888359/creating-pdf-file-in-powerbuilder –  Mar 26 '15 at 13:15
0

It looks to me like you need to have a reference to the 'activeworkbook'. This would be of type OLEobject so the declaration would be similar to: OLEobject lole_workbook.

Then you need to set this to the active work book. Look for the VBA code on Excel (should be in the Excel help) for something like a 'getactiveworkbook' method. You would then (in PB) need to do something like lole_workbook = ole_excel.application.activeworkbook

This gets the reference for PB to the activeworkbook. Then do you saveas and etc. like this lole_workbook.SaveAs(ls_DocPath,17)

Matt Balent
  • 2,337
  • 2
  • 20
  • 23
  • Thank you Matt. The problem was, powerbuilder did not know which worksheet/workbook it needs to save. There are 3 sheets and I needed to save the first one. Your answer helped in the right direction. – Vijay Raghavan Apr 22 '15 at 08:21
  • Just one more thing, I do the following to quit the excel and close the excel sheet. After I close the application, I tried to open the Excel sheet to edit it in MS Excel. The MS excel opens alright, but it is empty(Not even grid lines). I am assuming that I did something wrong while closing the excel. 
    ole_excel.application.workbooks(1).Save();
ole_excel.Application.Quit;
ole_excel.DisconnectObject();
     – Vijay Raghavan Apr 22 '15 at 08:25