I want to convert PI Datalink report to PDF through windows task scheduler service as we want to send this report to plant operators everyday as attachment. Please let me know how can I perform this.
thanks & regards,
Instead of relying on PI DataLink, I would recommend you try SQL Server Reporting Services, which allows you to create a report much similar to Excel. Then your plant operators can subscribe via the built-in email functionality.
Can I use PI Datalink functions. Is there any samples, so will try if any suits for our requirement.
You would need to use the PI OLEDB provider to connect to the PI System. Getting data from PI to the report is then a matter of writing an SQL query to return the data you want to see.
Install the PI OLEDB Classic/Enterprise in the same server as SQL Server Reporting Services (SSRS), and then use the Wizard functionality in SSRS to help you get started with designing your report and bringing the data in. (Your options are either to go directly from the report to PI using the PI OLEDB providers, or setting up a Linked Server to PI in SQL Server - and then using that linked server to gain access to PI data. I usually do the latter)
But, in our case our report development is completely different. I am attaching images of our report. If you observe first image in that sheet 1 is the report design and format. The second image where we are taking dates and pi datalink functions to calculate the report and assigning the values to the sheet 1.
Through PI Oledb I think we cannot design this format.
sorry 1st image is not attached. Is there any option of attaching file.
If you search on the web for "Converting Excel spreadsheets to PDF", you will find some interesting pages like this one.
Hope it helps!
If you are using newer versions of Excel (it looks like you have 2010), you should be able to use Excel's internal functionality to save as a PDF.
For example, within an Excel VBA macro, this line saves the file as a PDF:
ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\temp\mypdf.pdf"
So you shouldn't need to go through the complications of invoking a print to create a PDF. You should be able to do similar automation from outside of Excel via a .NET application or some other scripting, etc.
With that said, automating Microsoft Office products can be difficult to troubleshoot sometimes oftentimes. You'll want to ensure you do a lot of testing.
In general, some tips I can think of off hand based on things I've run into in the past:
1) When you select a specific user to run the scheduled task as (e.g. to invoke your custom script or .NET application, which automates Excel to run the ExportAsFixedFormat function), make sure that before you let it run in the scheduler, that you log into your computer as that user, start up Excel and complete any new user initialization. (E.g. the "Enter your Name and Initials" dialog, etc).
2) If you have macros or other content in your Excel report that Excel may typically pop-up a security warning dialog, or similar, make sure you either disable those types of prompts for that user, or, remove that functionality from your report if not needed.
In general, when you automate Excel, you have no control over any interactive dialogs that Excel may try to open. Since Excel was designed for human interaction, these dialogs will hang up your automation (while it is waiting for your to acknowledge a dialog, for example).
Also, if you are doing any .NET automation of Excel, be mindful of cleaning up the Excel objects carefully: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects
Ultimately, after having done some Excel automation in the past (and also working with some other common vendor products that have done this in the past, including OSIsoft's own RtReports product), I've found that I would typically avoid trying to automate Microsoft Office products in a non-interactive setting unless it was a last resort.
I would echo Asle's suggestion to investigate PI OLEDB and a proper reporting package such as SSRS for creating an automated reporting solution. You won't be able to use "DataLink functions" per se, but, in general, the functionality you find in DataLink should be available in PI OLEDB.
Thank you for your reply.
We are able to convert excel pi datalink report to pdf. we wrote "ExportAsFixedFormat" method in 'excel workbook open' method. We are opening excel through vbs script to convert excel report to pdf automatically. It is converting to pdf when we open excel normally as well as we run vbs script through command prompt, but when we schedule through task scheduler what ever PI Datalink functions values are available those are getting '#name?' error. We tried many articles in google but nothing helped out.
What could be the problem. I am attaching our workbook open method and vbs script code for reference.
With ActiveSheet '.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="D:\New folder\sample.pdf", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End With
Vbs Script method
Dim args, objExcelDim sDay, sMonth, sYear, sDate, sDir, sFileNameSet args = WScript.ArgumentsSet objExcel = CreateObject("Excel.Application")objExcel.Workbooks.Open args(0)objExcel.Visible = TrueobjExcel.Run Workbook_OpenobjExcel.Visible = 0objExcel.ActiveWorkbook.SaveobjExcel.ActiveWorkbook.Close(0)objExcel.Quit
I've found this article helpful in understanding why you should try to avoid this / and what to resolve when using Office automation: support.microsoft.com/.../257757
Technically not preferred, but for many business reasons Excel automation is still used quite often..
What user are you setting up the scheduled task to run as? Is it the same user that you tested the working VBS script as?
If it is a new/different user, are you able to try logging on as that user, opening Excel and verifying that DataLink is properly registered and working for that user's account?
with the same user only we scheduled the task. The task is running properly if it is set as 'When User is logged on'. If we set 'Whether User is logged in or not', the task is not running. Still we are searching for the solution.
Try to record the exception error message and paste it here. It will give us an idea of where the source of problem is.
Well, that's the common problem with Office Automation...It's not supposed to work, so finding your way around issues like these can be difficult.
On windows server 2003 not too many issues, but from 2008 upwards, the trouble starts due to increased security by Microsoft. I've done this a few times, but every single situation proved different, so there's no single golden bullit that will work. Sorry, but can provide little help.
One workaround might be to use some scripting to logon the user that is running the task on the console session, and then allow interaction with the session or run on the console session: "if you can't run it in the background, run it in the foreground".
I managed to get something like this working on my Windows 7 computer after a bit of testing. However, as Roger notes, Office Automation is unpredictable at best. So if you are able to reimplement your report in a non-Excel format that is designed for automated generation, that would make your automation much more robust and reliable.
I could not get it to work with a VBS script this time, but instead I:
1) Created a Macro-enabled workbook that on open exported itself as a PDF and exited
2) Save this Macro-enabled workbook in a trusted location (so that the Macros will run without needing to "Enable Macros" upon opening; see: http://office.microsoft.com/en-001/excel-help/add-remove-or-change-a-trusted-location-HA010354311.aspx)
3) Create a scheduled task that opens Excel directly using the full path to the new Macro-enabled workbook as the parameter (set as "Run whether user is logged on or not" and "run with highest privileges")
This seemed to work for me. The code I have in the workbook is:
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Sheet1.ExportAsFixedFormat xlTypePDF, "C:\New Folder\result.pdf"
Is publishing an Excel report as a webpage using Excel services and DataLink Server (with SharePoint) an option?
Thank you very much for your reply.
By using the above script i have pasted, it is working properly in windows 7 with both the options 'Whether user is logged or not' and 'When use is logged on'. But when we deploy this in production server windows server 2012 we are facing problem only report is running with the option 'Only when user is logged in'.
What exception message need to paste. While running task it is not giving any error message, but it is showing the status as operation completed successfully, but our pdf file is not generating.
Could you possibly write a macro to copy and paste paste the values (so that there are no PI datalink functions associated with the cell) of the report on to a separate worksheet and then have your macro save that sheet to pdf? This would avoid the issue with #NAME? values showing up on the PDF and it could be sent to anyone. Not sure if this is what you're looking for but we do this in our company and it works.
Retrieving data ...