If you want to save yourself a lot of time and effort, Excel intgration (report and send data back) has been done for Axapta...take a look at www.atlasxl.com.au
Regards,
Steve.
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Satish
Sent: Friday, 24 March 2006 6:17 PM
To: [email protected]
Subject: Re: SV: SV: [development-axapta] Working with Excel through Axapta
Hi Mikael,
Your code was very helpful! Thanks so much :)
One part of my problem is solved. Now, i'm able to export Ax data to excel.
My other requirement is to import the excel data into Axapta. For this i wrote the following code...
-----------------------------------------------
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorkSheets sheets;
SysExcelWorkSheet sheet;
str _filepath = "C:\\SatishExcelTest.xls";
loginProperty loginProperty = new LoginProperty();
ODBCConnection odbcConnection;
Statement sql;
ResultSet resultSet;
str sqlstr = 'select studid,name,age from [Sheet1
$]';
;
super();
try{
excel = SysExcelApplication::construct();
books = excel.workbooks();
books.open(_filepath);
sheets=excel.worksheets();
sheet=sheets.itemFromNum(1);
loginProperty.setDSN('SatishExcelTestODBC'); // (Name of your ODBC configuration)
odbcConnection = new ODBCConnection(loginProperty);
sql = odbcConnection.createStatement();
resultSet = sql.executeQuery(sqlstr);
// select data from excel and input fields into our table
while(resultSet.next()) {
satishTable.StudId=resultSet.getString(1);
satishTable.Name=resultSet.getString(2);
satishTable.Age=resultSet.getInt(3);
}
resultSet.close();
books.close();
}
catch {
books.close();
}
-----------------------------------------------------------
When i run this code, i get an error message 15:37:59 which says "[Microsoft][ODBC Excel Driver] The connection for viewing your linked Microsoft Excel worksheet was lost."
Pls help!!! :(
Regards,
Satish Dalal
--- In [email protected], Mikael Dalgård (GPV International A/S) <[EMAIL PROTECTED]> wrote:
>
> Hi Satish
>
> Example: (included Excel file, place the included file like this:
C:\filename.xls)
>
> Source code, look below:
>
> --
>
> static void COMExcelExample(Args _args) {
> custtable customers;
>
> SysExcelApplication SysExcelApplication;
> SysExcelWorksheet SysExcelWorksheet;
> SysExcelWorksheets SysExcelWorksheets;
> SysExcelWorkbooks SysExcelWorkbooks;
> SysExcelWorkbook SysExcelWorkbook;
> SysExcelRange SysExcelRange;
> SysExcelCell SysExcelCell;
> SysExcelCells SysExcelCells;
> COMVariant COMVariant1;
> #excel
>
> int i = 1, whilecount;
>
> SysOperationProgress progress = new SysOperationProgress();
>
> ;
>
> while select count(RecId) from customers
> WhileCount = customers.RecId;
>
> sysExcelApplication = SysExcelApplication::construct();
> sysExcelApplication.visible(false);
> sysExcelWorkbooks = sysExcelApplication.workbooks();
> COMVariant1 = new COMVariant();
> COMVariant1.bStr('c:\\filename.xls');
> sysExcelWorkbook = sysExcelWorkbooks.add(COMVariant1);
> SysExcelWorksheets = sysExcelWorkbook.worksheets();
> SysExcelWorksheet = SysExcelWorksheets.itemFromNum(1);
> SysExcelRange = SysExcelWorksheet.cells().range
(#ExcelTotalRange);
>
> progress.setCaption('Progress');
> progress.setText(strfmt('%1 - %2','Exporting customers to
Excel','Please wait'));
> progress.setTotal(whileCount);
>
> while select * from customers
> if (customers)
> {
> i++;
> SysExcelCell = SysExcelWorksheet.cells().item(i,1);
> SysExcelCell.value(customers.AccountNum);
> SysExcelCell = SysExcelWorksheet.cells().item(i,2);
> SysExcelCell.value(customers.Name);
>
> progress.incCount();
> }
>
> sysExcelApplication.visible(true);
> }
>
> --
>
> Hope you'll find this helpful :-)
>
>
> Best regards
>
> Mikael Dalgaard
>
> -----Oprindelig meddelelse-----
> Fra: Mikael Dalgård (GPV International A/S)
> Sendt: 23. marts 2006 09:03
> Til: '[email protected]'
> Emne: SV: SV: [development-axapta] Working with Excel through
Axapta
>
> Hi Satish
>
> ... xinfo::directory(directoryType::Appl).... is the Axapta appl.
path, if you would like to use [C:\] instead, like
this: "C:\\satishExcelTest.xls", try to change the COMVariant1 code
into:
>
> COMVariant1.bStr('C:\\SatishExcelTest.xls');
>
>
> Best regards
>
>
> Mikael Dalgaard
> -----Oprindelig meddelelse-----
> Fra: [email protected] [mailto:development-
[EMAIL PROTECTED] På vegne af Satish
> Sendt: 22. marts 2006 14:19
> Til: [email protected]
> Emne: Re: SV: [development-axapta] Working with Excel through
Axapta
>
> Hi Mikael,
>
> Thanx a lot for ur help!
>
> However, I'm unable to use the COMVariant part of ur code; especially
> the part where
>
> COMVariant1.bStr(xinfo::directory(directoryType::Appl)
> + 'C:\\SatishExcelTest.xls);
>
> The compilation of this line was giving a syntax error. I'm really new
> to Axapta, so i couldn't get it cleared... :(
>
> Can u suggest a remedy for this? :)
>
> Thanks,
> Satish
>
>
> --- In [email protected], Mikael Dalgård (GPV
> International A/S) <mid@> wrote:
> >
> > Hi there
> >
> > Try this :-)
> >
> > SysExcelApplication SysExcelApplication;
> > SysExcelWorksheet SysExcelWorksheet;
> > SysExcelWorksheets SysExcelWorksheets;
> > SysExcelWorkbooks SysExcelWorkbooks;
> > SysExcelWorkbook SysExcelWorkbook;
> > SysExcelRange SysExcelRange;
> > SysExcelCell SysExcelCell;
> > SysExcelCells SysExcelCells;
> > COMVariant COMVariant1;
> > #excel
> > ;
> > sysExcelApplication =
> SysExcelApplication::construct();
> > sysExcelApplication.visible(false);
> > sysExcelWorkbooks =
> sysExcelApplication.workbooks();
> > COMVariant1 = new COMVariant();
> > COMVariant1.bStr(xinfo::directory(directoryType::Appl)
+ 'your
> filename.xls');
> > sysExcelWorkbook = sysExcelWorkbooks.add
> (COMVariant1);
> > SysExcelWorksheets =
> sysExcelWorkbook.worksheets();
> > SysExcelWorksheet =
> SysExcelWorksheets.itemFromNum(1);
> > SysExcelRange = SysExcelWorksheet.cells().range
> (#ExcelTotalRange);
> >
> > SysExcelCell = SysExcelWorksheet.cells().item(1,1);
> > SysExcelCell.value("put in you value here");
> >
> >
> > Best regards
> >
> > Mikael Dalgaard
> >
> >
> > -----Oprindelig meddelelse-----
> > Fra: [email protected] [mailto:development-
> [EMAIL PROTECTED] På vegne af cod
> > Sendt: 20. marts 2006 21:33
> > Til: [email protected]
> > Emne: Re: [development-axapta] Working with Excel through Axapta
> >
> > On 20/03/06, Satish <satishdalal@> wrote:
> > >
> > > Hi guys,
> > >
> > > I have a situation where i need to open,edit and save excel
files
> > > through forms in Axapta.
> > >
> > > I need to allow Excel to do the processing of the data, and
then
> store
> > > the computed values in Axapta tables.
> > >
> > > Thanks in anticipation.
> >
> >
> > You can import into AXAPTA external COM object of Excel ... try
> with google
> > to get example on how invoke excel methods..
> >
> > cod
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Yahoo! Groups Links
YAHOO! GROUPS LINKS
- Visit your group "development-axapta" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
