This week I have been investgating an issue with the Excel import in Cost Accounting->CostTransactions->Import button.
When I click the import button the Excel import wizard comes up, I click next, then I click “Open workbook”. At this point I expect to see an Excel “open file” dialog box, but I don’t get one, nothing appears to happen. However if I press alt+tab I find that the dialog box is there in the background it was just that it didn’t get focus when it opened, so I can find my Excel file and carry on with the import.
It is possible to allow the Excel dialog to gain focus automatically with a small code change in Classes\COSExcelImport.
public boolean openWorkBook()
{
#COSStrings
COMVariant fileName;
COMVariant fileFilter;
int infoLine = infolog.line();
;
fileName = new COMVariant();
fileFilter = new COMVariant();
fileFilter.bStr(“@SYS28576” + ‘ ‘ + ‘(*.xls; *.xlsx), *.xls; *.xlsx’);
try
{
WinAPI::setForegroundWindow(WinAPI::findWindow(”, ‘Microsoft Excel’)); //NEWLINE
fileName = application.getOpenFileName(fileFilter);
if (!fileName.boolean())
{
// Cancel button was selected.
info(“@SYS59931”);
return false;
}
else
{
workBooks = application.workBooks();
workBooks.open(fileName);
workSheets = application.worksheets(); //Get access to the collection of worksheets
}
}
catch (Exception::Error)
{
infolog.clear(infoLine);
throw error(“@SYS59929”);
}
application.visible (false);
return true;
}
To explain why I chose this particular type of fix – because I also explored other possibilities – such as starting a timed method which will set focus directly to the open file dialog once it is opened (as when the dialog is opened code stops running), using infolog.addTimeOut(). However I found that even a timed method started before opening the dialog will not run once the dialog is opened until the dialog is closed. I also explored the idea of using a method of Excel.Application to set focus to the dialog, but it appears there isn’t a function/method available to do this as standard.
This issue only occurs with Windows 2008 R2 and Windows 7. It occurs in AX4 and AX2009. It has been officially registered with Microsoft and will currently be dealt with in a future version/service pack, of course we will re-evaluate that status if we find a company that is significantly impacted by it.