Task Manager Lock Excel Sheet

Finance and Operations/AX Developer Forum
1

Hi All,

I am facing Excel Lock problem in Task Manager. whenever i upload vendor detail through X++ then task manager

lock excel sheer every time. I am also paste the code given below:

AX Version AX2012R3

private void readExcel()
{
DirAddressBookParty addressParty;
VendTable vendTable;
TaxInformationVendTable_IN taxInfo;
VendBankAccount vendBankAccount;
VendAccount vendAccount;
Numberseq numSeq;
VendGroup vendGroup;
COMVariantType type;
//Name name;
COMVariant variant;
int rowCount, row = 1;
NoYes enumType;
NatureOfAssessee_IN natureOfAssess;
container valueCon;
str natureOfAssessStr;

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return “”;
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return “”;
}

recordInsertVend = new RecordInsertList(tableNum(VendTable));
recordInsertTaxInfo = new RecordInsertList(tableNum(TaxInformationVendTable_IN));
recordInsertBank = new RecordInsertList(tableNum(VendBankAccount));
recordAddressBook = new RecordInsertList(tableNum(DirAddressBookParty));

application = SysExcelApplication::construct();
application.displayAlerts(false);

workbooks = application.workbooks();

try
{
workbooks.open(fileName);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

workbook = workbooks.item(1);
workbook.readOnly();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row++;

rowCount = this.findLastRow(worksheet);

simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Data Uploading’, rowCount);

do
{
//valueCon = conIns(valueCon, 1, COMVariant2Str(cells.item(row, 1).value())); // Vendor Account
//valueCon = conIns(valueCon, 1, COMVariant2Str(cells.item(row, 2).value())); // Vendor Group
// valueCon = conIns(valueCon, 2, COMVariant2Str(cells.item(row, 3).value())); // TDS Group
// valueCon = conIns(valueCon, 3, COMVariant2Str(cells.item(row, 6).value())); // Bank Account
// valueCon = conIns(valueCon, 4, COMVariant2Str(cells.item(row, 10).value()));// RO Code
// valueCon = conIns(valueCon, 5, COMVariant2Str(cells.item(row, 11).value())); // Address Book

//this.checkValidation(valuecon); //Check Validation

//ttsBegin;
numSeq = Numberseq::newGetNum(VendParameters::numRefVendAccount());
vendAccount = numSeq.num();
numSeq.used();
//ttsCommit;

vendTable.AccountNum = vendAccount;
name = COMVariant2Str(cells.item(row, 1).value());
vendTable.InvoiceAccount = vendTable.AccountNum;
vendTable.VendGroup = COMVariant2Str(cells.item(row, 2).value());
vendTable.BankAccount = COMVariant2Str(cells.item(row, 4).value());
vendTable.ROAccountNum = COMVariant2Str(cells.item(row, 10).value());
vendTable.Party = this.updatePartyRecord(vendTable, name);
vendTable.Currency = CompanyInfo::standardCurrency();
vendTable.SegmentId = COMVariant2Str(cells.item(row, 12).value());
vendTable.SubsegmentId = COMVariant2Str(cells.item(row, 13).value());
vendTable.CspCode = COMVariant2Str(cells.item(row, 14).value());

taxInfo.VendTable = vendTable.AccountNum;
natureOfAssessStr = COMVariant2Str(cells.item(row, 9).value());
//taxInfo.TCSGroup = COMVariant2Str(cells.item(row, 5).value());
taxInfo.TDSGroup = COMVariant2Str(cells.item(row, 3).value());
taxInfo.PANNumber = COMVariant2Str(cells.item(row, 8).value());
taxInfo.NatureOfAssessee = str2enum(natureOfAssess,natureOfAssessStr);
taxInfo.Name = COMVariant2Str(cells.item(row, 7).value());

if(taxInfo.PANNumber!= “”)
{
taxInfo.PANStatus = PANStatus_IN::Received;
}
else
{
taxInfo.PANStatus = PANStatus_IN::NotAvailable;
taxInfo.PANReferenceNumber = this.OXI_PanReferenceNo();
if(taxInfo.PANReferenceNumber)
{
taxInfo.IsPANReferenceGenerated = NoYes::Yes;
}
else
{
taxInfo.IsPANReferenceGenerated = NoYes::No;
}
}
if(taxInfo.TDSGroup != “”)
{
vendTable.TaxWithholdCalculate = NoYes::Yes;
}
if(COMVariant2Str(cells.item(row, 6).value()))
{
vendBankAccount.VendAccount = vendTable.AccountNum;
vendBankAccount.AccountID = COMVariant2Str(cells.item(row, 4).value());
vendBankAccount.Name = COMVariant2Str(cells.item(row, 5).value());
vendBankAccount.AccountNum = COMVariant2Str(cells.item(row, 6).value());
vendBankAccount.RegistrationNum = COMVariant2Str(cells.item(row, 15).value());
}

addressParty.AddressBook = DirAddressBook::find(COMVariant2Str(cells.item(row, 11).value())).RecId;
addressParty.Party = vendTable.Party;

recordAddressBook.add(addressParty);
recordInsertVend.add(vendTable);
recordInsertTaxInfo.add(taxInfo);
if(COMVariant2Str(cells.item(row, 6).value()))
recordInsertBank.add(vendBankAccount);

row++;
type = cells.item(row, 1).value().variantType();
simpleProgress.incCount();
simpleprogress.setText(strfmt(“Status: %1”, row));

}while (type != COMVariantType::VT_EMPTY);

recordInsertVend.insertDatabase();
recordInsertTaxInfo.insertDatabase();
recordInsertBank.insertDatabase();
recordAddressBook.insertDatabase();
application.quit();

info(strFmt("%1 Vendor have been uploaded successfully", rowCount - 1));
}

Kindly help me.

Thanks in Advance.

2

What exactly do you mean by “Excel Lock problem in Task Manager”?

3

Actually Martin,
when i upload excel sheet data into AX then its uploading successfully but when i try to open excel sheet
it is not allow me to open excel sheet while i manually end session from the ‘task manager’.
This is the actually problem and i don’t understand what it is behaving like this.

4

Is the Infolog message being displayed ?
Have you tried debugging, Is the code reaching the info message ?
Not unable to find a Workbook Close method in your code. Probably that’s the issue and unable to close excel worksheet & connection , try putting it before “application.quit();”

5

Sorry for the grammar above it was typo error,
Put the below code

application.displayAlerts(false);

before
“application.quit();”
and see if it works.

6

You told us neither how you’re “uploading” the file, why you can’t (e.g. whether you got an error and what the message said) nor what process you closed in Task Manager. You must realize that we can’t do much for you if you don’t tell us enough about your problem.

Let me guess. You have a piece of X++ code for “uploading” the file (please tell us what it actually means) and then you’re trying to open the same file via SysExcel* classes (as shown in the snippet above), but it fails with an error saying that the file is locked by another process. You know what process it is (but forgot to tell us), therefore you go to Task Manager and kill it. It clearly means that the process still have the file open in the process, therefore you must close the file before you can open it in AX. Please confirm whether my guess is right and add more details.

I’ll also move this question to the Developer forum, because this looks like a problem in your customization.

7

Hi Martin,
This is the simply piece of code which is use to upload excel sheet.
The main problem is that whenever i upload excel sheet through the above code its working fine but when i open
open same file then its not allow me to open again while i close the session through task manager.
BTW it is not showing me any infolog. (it is not allow me to reuse same file).

8

Hi, Venkat,
It is not showing any info message.
its just not allow me to open same excel sheet when i upload through code while i am not close this excel from task manager.

9

Aha, so “to upload” actual means to “to open through SysExcel* classes”. You already open the file by workbooks.open(fileName), so if you’re trying to open it again for some reason (unknown to me), it’s not possible because it’s already open by yourself. You must close it first.

I still miss your description of what happens. You said you don’t get any error message, but it didn’t tell us what does happen, such as that execution freezes at a particular line of code.

10

Taukeer, Ok so that means it never reached the Info message there.
Which means the excel still being used by your X++ code
Like I said earlier you didn’t have
“workbooks.close();”
method anywhere and also it could well be with the file permissions.
perm = new FileIOPermission(#FileName, #FileMode);
perm.assert();
To what point you were able to reach through debugger did you try debugging the code?

Just to give some heads up see the example here and try to follow and wrap the code in try catch
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();

see Example: ashokkancheti.blogspot.com/…/excel-file-import-using-x-code.html
Hope that helps.