excel automation printing to pdf error

[ravicoder]

Hi all

I am using Excel automation for reporting in my vfp application.
there are certain reports I need to generate an excel as well as a PDF output. the output is properly formatted as per requirement in Excel
I tried out the excel export feature for which the code is as below

oExcel = Createobject('Excel.application')
excelfilename=fullpath(curdir()) + "excelxxxxx"

... (content)
...

savelocation = excelfilename + ".pdf"
oExcel.Activeworkbook.ExportAsFixedFormat(0,savelocation,0,.f.,.t.,.f.,.f.,.t.)

I am getting an error as

Ole dispatch exception code 0 from microsoft excel. Document not saved. Error encountered while saving

Any help on this would be highly appreciated

Thanks
Ravi

 

[mJindrova]

Hi,

Base question is... what MS office version you use?


mJindrova

 

[tomk3]

Hello,

can you try oExcel.Activeworkbook.ExportAsFixedFormat(0,savelocation,0) ?
Are you allowed to store in the path of savelocation (for example c:\windows or "program files" can give a problem) ?

regards
tom

 

[Mike Lewis]

I'm worried about the 6th and 7th arguments to ExportAsFixedFormat(). Accordoing to the docs, these should specify the page range of the document to be exported. Passing .F. does not do that.

So, on that basis, you should do something like this:

oExcel.Activeworkbook.ExportAsFixedFormat(0,savelocation,0,.f.,.t.,[highlight #FCE94F][b]1,5[/b][/highlight],.t.)

Here,I have set the page range as 1 to 5, indicating that I want to print the first five pages only. Adjust this to match your requirements. If you don't know how many pages are to be printed, set the second of the these figures to any arbitrarily high number. (The maximum number of pages is over 2 billion.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Tom,

According to the docs, the location can include the path, or it can be just the filename, in which case the file will be saved in the "current" folder (that is, Excel's current folder, not VFP's).

More info here:

https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ravicoder]

Thanks all

I figured out what I was doing wrong.
I changed the code to oExcel.Activeworkbook.ExportAsFixedFormat() as tom suggested and it works

Now I came across another issue i.e. when my excel workbook has multiple sheets generated, the output does not follow the same layout as the first sheet. From the 2nd page onwards the layout is all distorted
Maybe I will have to set the layout options individually for 2nd sheet onwards. Anyone has any ideas on this?

Thanks

 

[tomk3]

Hi Mike,

i see, but I thought that his savelocation (using curdir) might contain a restricted folder if its installed and started from "program files"


regards
tom

 

[Mike Lewis]

Yes, good point, Tom.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[combo]

Now I came across another issue i.e. when my excel workbook has multiple sheets generated, the output does not follow the same layout as the first sheet. From the 2nd page onwards the layout is all distorted

Excel export to pdf is in fact printing to pdf, with current page setups for each sheet, plus additional settings. So you need to either set page setups for each sheet or use preformatted workbook or template, open it (or create new workbook from template) and fill it and next export.
A lot of code you can get working in excel and recording actions.