Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to create a batch ...
Finance | Project Operations, Human Resources, ...
Suggested answer

How to create a batch class to import an excel sheet to this form

(0) ShareShare
ReportReport
Posted on by ax.tech 86

Hi Experts,

I want to know how to Create a batch class to import an excel sheet to the form

    1. Path: Path: AP-> Common -> Amortization Header upload
    2. New dialog will open for Browse
    3. Press OK
    4. This will upload excel data to the above form.

Thanks in advance for your valuable time.

  • Komi Siabi Profile Picture
    Komi Siabi 12,772 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form

    Please, let us know if there error is resolved and mark answer(s) that worked for you.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi,

    Thanks a lot Komi and Martin for your valuable time and support.

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,772 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hello,

    Just like Martin said, you need to have a method on your class.

    I suggest you use a job instead of the class to make it more easier. Create a Job and paste your code in there then run it.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    class ImportAmortizationHeader
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
ImportAmortizationHeader importAmortizationHeader;
int row;
date _startDate;
date _endDate;
int _numberOfPeriods;
str _vehicleNumber;
str _invoiceNumber;
str _documentNumber;
str _account;
real _debitAmount;
real _creditAmount;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "Path\\filename.xlsx";
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number
cells = worksheet.cells();
do
{
row  ;
_startDate = cells.item(row, 1).value().bStr();
_endDate = cells.item(row, 2).value().bStr();
_numberOfPeriods = cells.item(row, 3).value().bStr();
_vehicleNumber = cells.item(row, 4).value().bStr();
_invoiceNumber = cells.item(row, 5).value().bStr();
_documentNumber = cells.item(row, 6).value().bStr();
_account = cells.item(row, 7).value().bStr();
_DebitAmount = cells.item(row, 8).value().bStr();
_creditAmount = cells.item(row, 9).value().bStr();


AmortizationHeader.StartDate = _startDate;
AmortizationHeader.EndDate = _endDate;
AmortizationHeader.NumberOfPeriods = _numberOfPeriods;
AmortizationHeader.VehicleNumber = _vehicleNumber;
AmortizationHeader.InvoiceNumber = _invoiceNumber;
AmortizationHeader.DocumentNumber = _documentNumber;
AmortizationHeader.Account = _account;
AmortizationHeader.DebitAmount = _debitAmount;
AmortizationHeader.CreditAmount = _creditAmount;
productType.insert();

type = cells.item(row 1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
    amortization.png

    Hi Komi Siabi,

    I have done as you said but still getting this.

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,772 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi ax.tech,

    So, let take a look at the errors.

    Line 1. The class already exists.

    This means there is already an existing class called. AmortizationHeader. Kindly rename this one to maybe ImportAmortizationHeader.

    Line 23. 

    You need separate your declaration from your logic by a semi-colon. so after real _creditAmount; you need ; at the next line.

    class ImportAmortizationHeader
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
AmortizationHeader amortizationHeader;
int row;
date _startDate;
date _endDate;
int _numberOfPeriods;
str _vehicleNumber;
str _invoiceNumber;
str _documentNumber;
str _account;
real _debitAmount;
real _creditAmount;
;
application = SysExcelApplication::construct();

  • Martin Dráb Profile Picture
    Martin Dráb 231,837 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    You forgot to create a method. You're trying to write code direcly in class header.

    Also, please start using line indentation - code is very difficult to read without it, which is bad for you and also for everyone else who wants to read your code. It's possible if you noticed the bug if you followed the right structure. Also, don't use underscore as the prefix of local variable names. This prefix should be used for method parameters.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    class AmortizationHeader
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
AmortizationHeader amortizationHeader;
int row;
date _startDate;
date _endDate;
int _numberOfPeriods;
str _vehicleNumber;
str _invoiceNumber;
str _documentNumber;
str _account;
real _debitAmount;
real _creditAmount;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "Path\\filename.xlsx";
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number
cells = worksheet.cells();
do
{
row  ;
_startDate = cells.item(row, 1).value().bStr();
_endDate = cells.item(row, 2).value().bStr();
_numberOfPeriods = cells.item(row, 3).value().bStr();
_vehicleNumber = cells.item(row, 4).value().bStr();
_invoiceNumber = cells.item(row, 5).value().bStr();
_documentNumber = cells.item(row, 6).value().bStr();
_account = cells.item(row, 7).value().bStr();
_DebitAmount = cells.item(row, 8).value().bStr();
_creditAmount = cells.item(row, 9).value().bStr();


AmortizationHeader.StartDate = _startDate;
AmortizationHeader.EndDate = _endDate;
AmortizationHeader.NumberOfPeriods = _numberOfPeriods;
AmortizationHeader.VehicleNumber = _vehicleNumber;
AmortizationHeader.InvoiceNumber = _invoiceNumber;
AmortizationHeader.DocumentNumber = _documentNumber;
AmortizationHeader.Account = _account;
AmortizationHeader.DebitAmount = _debitAmount;
AmortizationHeader.CreditAmount = _creditAmount;
productType.insert();

type = cells.item(row 1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
    amort.png

    Hi komi siabi,

    I have attached the code and errors. 

    Regards

  • Komi Siabi Profile Picture
    Komi Siabi 12,772 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi,

    Could you please, show us the error you are getting ?.

    Please, when pasting your codes,

    Use rich formatting -> Insert code

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi Komi Siabi,

    I have created a class

    class AmortizationHeader

    {

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

    AmortizationHeader amortizationHeader;

    int row;

    date _startDate;

    date _endDate;

    int _numberOfPeriods;

    str _vehicleNumber;

    str _invoiceNumber;

    str _documentNumber;

    str _account;

    real _debitAmount;

    real _creditAmount;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specify the file path that you want to read

    filename = "Path\\filename.xlsx";

    try

    {

    workbooks.open(filename);

    }

    catch (Exception::Error)

    {

    throw error("File cannot be opened.");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number

    cells = worksheet.cells();

    do

    {

    row++;

    _startDate = cells.item(row, 1).value().bStr();

    _endDate = cells.item(row, 2).value().bStr();

    _numberOfPeriods = cells.item(row, 3).value().bStr();

    _vehicleNumber = cells.item(row, 4).value().bStr();

    _invoiceNumber = cells.item(row, 5).value().bStr();

    _documentNumber = cells.item(row, 6).value().bStr();

    _account = cells.item(row, 7).value().bStr();

    _DebitAmount = cells.item(row, 8).value().bStr();

    _creditAmount = cells.item(row, 9).value().bStr();

    AmortizationHeader.StartDate = _startDate;

    AmortizationHeader.EndDate = _endDate;

    AmortizationHeader.NumberOfPeriods = _numberOfPeriods;

    AmortizationHeader.VehicleNumber = _vehicleNumber;

    AmortizationHeader.InvoiceNumber = _invoiceNumber;

    AmortizationHeader.DocumentNumber = _documentNumber;

    AmortizationHeader.Account = _account;

    AmortizationHeader.DebitAmount = _debitAmount;

    AmortizationHeader.CreditAmount = _creditAmount;

    productType.insert();

    type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    }

    but getting syntax error please help and let me where I am wrong

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,772 Most Valuable Professional on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi Ax.Tech,

    Create an action menu item and call up your class in the property, add it to the form action pane.

    When you click on it, you will see the window to upload your excel file.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,001 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,837 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans