My 2p about ERP Solutions, Information Worker Solutions and other software products (mainly Microsoft Dynamics AX and Microsoft SharePoint).

31 August 2009

Links List August 2009

I decided to share all my interesting reads and resources month by month with my blog readers. You can find these posts by searching on the label Links. I'll try to order the resources in logical categories. If you would like to see some interesting stuff added in the next month, don't hesitate to post a comment.

So this is my Links post for August 2009.

SharePoint - Design and Customization

Dynamics AX


Continue reading......

by Patrik Luca 0 comments

28 August 2009

The path or file name is invalid error when opening Office document in a SharePoint library

Problem Description: The path or file name is invalid

You browse to a SharePoint document library and when you click on the Name of a Microsoft Office PowerPoint file stored in the library, you receive following error: The path or file name for http://servername/libraryurl/filename.pptx is invalid. Please check that the path and file name are correct. Your PowerPoint application starts automatically up on your desktop, but the file isn’t opened: however, yesterday everything worked fine…

What is even weirder, if you do a search for the document and you click on its name in the search results, your PowerPoint application starts and the file opens without problem.

What is also remarkable: if you go to your document library and you choose Edit in Microsoft Office PowerPoint, your PowerPoint application starts, the file gets checked out automatically and opens without error.

Solution: open the document via PowerPoint directly

  1. Start Microsoft Office PowerPoint 2007 on your desktop.
  2. Click the Microsoft Office Button in the upper left of the screen.
  3. Choose Open.
  4. Enter the url of the SharePoint document library in the File name box. (Remark: make sure it is the url of the library and not the url of the PowerPoint filename which fails to open, because this won’t fix the problem)
  5. All files stored in the document library will be shown in the right part of the Open file pop-up window.
  6. Choose the PowerPoint file: it will open in your PowerPoint application.
  7. Close your PowerPoint application.

If you now go back to the SharePoint document libary with Internet Explorer, you’ll be able to open PowerPoint files stored in it, by merely clicking their Name in Internet Explorer.

I really don’t know why the problem suddenly arises, nor why it is solved by following the procedure described above, but it solves the problem…

I experienced also that you have to do this procedure for each SharePoint document library having the problem. But you have to do it only once per library: if you do it for one PowerPoint slideshow in the library, all others will open again without error.


Continue reading......

by Patrik Luca 2 comments

14 August 2009

Export to Excel with X++ code

Introduction

Every user of Microsoft Dynamics AX knows the possibility and the ease-of-use to copy-paste data from a Dynamics AX grid to a Microsoft Excel worksheet.

However, as Dynamics AX developer, you can achieve the same through X++ code, with a lot of more possibilities. In this post, I show an example of such an export to Microsoft Excel. I am going to export the list of my customers to a first worksheet in my Excel file. For each customer, I’ll add a seperate worksheet through X++ code and I’ll export in that particular worksheet an overview of backorder lines with their quantity that has not yet been packing slip delivered.

X++ code

Open the Application Object Tree (AOT) and create a new Job.

Copy-Paste underneath code and run the Job. The Job will create a Microsoft Excel workbook in the directory C:\Windows\Temp.


static void ExportToExcel(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks sysExcelWorkBooks;
// Filename to which you will be writing your data
FileName fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx";
SysExcelWorkbook sysExcelWorkBook;
SysExcelWorkSheets sysExcelWorkSheets;
SysExcelWorkSheet sysExcelWorkSheet;
SysExcelWorkSheet sysExcelWorksheetBackOrder;
SysExcelWorksheet sysExcelWorkSheetToBeDeleted;
int row = 1;
int rowBackOrder;
CustTable custTable;
SalesTable salesTable;
SalesLine salesLine;
boolean workSheetAdded = false;
int nbrOfCustomers;
;

// Initialising progress bar
progress.setCaption("Export To Excel in progress...");
progress.setAnimation(#AviTransfer);
// Initialisation of some objects
sysExcelApplication = SysExcelApplication::construct();
// Create new workbook
sysExcelWorkBooks = sysExcelApplication.workbooks();
sysExcelWorkBook = sysExcelWorkBooks.add();
// Get worksheets collection
sysExcelWorkSheets = sysExcelWorkbook.worksheets();
// Excel visible on desktop running the job or not?
sysExcelApplication.visible(false);
// Newly created Excel files have by default some worksheets
// Delete those worksheets created by default
while(sysExcelWorkSheets.count() > 1)
{
sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);
sysExcelWorkSheetToBeDeleted.delete();
}
// Add as many worksheets as there are customers
select count(RecId) from CustTable;
sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId);
// Add another worksheet
sysExcelWorkSheet = sysExcelWorkSheets.add();
//Rename the first worksheet
sysExcelWorkSheet.name("Customers");
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorkSheet.cells().item(1,1).value("Customer account");
// set a value in cell on row 1 column 2
sysExcelWorksheet.cells().item(1,2).value("Name");

while select custTable
{
progress.setText(strfmt("Customer %1", custTable.Name));
row++;
rowBackOrder = 1;
sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum);
sysExcelWorksheet.cells().item(row,2).value(custTable.Name);
while select salesLine
where salesLine.SalesStatus == salesStatus::Backorder
&& salesLine.ConfirmedDlv < Today()
&& salesLine.RemainSalesPhysical > 0
join salesTable
where salesTable.SalesId == salesLine.SalesId &&
salesTable.CustAccount == custTable.AccountNum
{
if(!workSheetAdded)
{
// Use the next Excel worksheet and rename it
sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum(
row);
//Name of worksheet can have maximum 31 characters
sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31));
workSheetAdded = true;
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorksheetBackOrder.cells().item(1,1).value(
"Ship Date");
// set a value in cell on row 1 column 2
sysExcelWorksheetBackOrder.cells().item(1,2).value(
"Item Number");
// set a value in cell on row 1 column 3
sysExcelWorksheetBackOrder.cells().item(1,3).value(
"Item Name");
// set a value in cell on row 1 column 4
sysExcelWorksheetBackOrder.cells().item(1,4).value(
"Deliver Remainder");
}
rowBackOrder++;
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value(
salesLine.ConfirmedDlv);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value(
salesLine.ItemId);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value(
InventTable::find(salesLine.ItemId).ItemName);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value(
salesLine.RemainSalesPhysical);
}
workSheetAdded = false;
}
// Suppress the pop-up window:
// A file named foo already exists in this location. Do you want to replace it?
sysExcelApplication.displayAlerts(false);
// Save the Excel file
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
// Make sure you close the Excel application
// Especially if you run the job without showing Excel on the desktop
// (sysExcelApplication.visible(false))
sysExcelApplication.quit();

}



The result should look like this if you execute the job on the demo data in Microsoft Dynamics AX 2009 with Microsoft Excel 2007:





This is the first worksheet in my Excel workbook with the complete list of my customers in it.





This is one of the other worksheets, being the one created for customer Forest Wholesales and showing all the backorder lines for this particular customer.



Remarks about some code snippets



  • sysExcelApplication.visible(false);

    If set to false, Microsoft Excel will be opened in the background of your desktop.


  • sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);

    sysExcelWorkSheetToBeDeleted is set to the second worksheet in your Excel workbook.


  • sysExcelWorkSheet.name("Customers");

    sysExcelWorksheet is renamed to Customers.


  • sysExcelApplication.displayAlerts(false);

    If set to true, a pop-up window (A file named ExportToExcel.xlsx already exists in this location. Do you want to replace it?) will appear if an Excel file with the same name already exists at the save location. This can be very annoying if you want to put this X++ code in a class and you want to run this class in batch at night. If set to true, your batch will wait until somebody gives a yes to allow replacement of the existing Excel file. If set to false, an existing Excel file will be overwritten.


  • sysExcelApplication.quit();

    Don’t forget this line of X++ code, or there will remain an Excel process running. This is important in the case where you did run Microsoft Excel in the background of your desktop. If you forget this line of X++ code and you run Excel in the background, you’ll have multiple Microsoft Excel processes after running this job a couple of times (you can check this by opening Task Manager and having a look at the Processes tab).


Continue reading......

by Patrik Luca 6 comments

Patrik Luca, Ieper, BELGIUM
Feel free to use or spread all of the content on my blog. In return, linking back to my blog would be greatly appreciated. All my posts and articles are provided "AS IS" with no warranties.

Subscribe feeds via e-mail
Subscribe in your preferred RSS reader

Subscribe feeds rss Most Read Entries

Categories

Recommended Books


Subscribe feeds rss Recent Comments

This Blog is part of the U Comment I Follow movement in blogosphere. Means the comment field of this blog is made DOFOLLOW. Spam wont be tolerated.

Live Traffic Feed

Recent Visitors

Blog Archive

My Blog List

Followers

Guest Links