Importing Data into Excel or Access

andreasn

Hello guys,

I'm sure you can help me out!

I would like to create an MS Excel or MS Access file to analysis data from our Nav Database.
My idea is; when I open the file there is vba code, that imports data from
the Nav Database. Is DAO possible? Has anyone done this before?

Another solution for me would be, if i could manage to create a dataport,
that creates a csv file every 24h. I could access that csv file and analysis it
in Excel or Access.

Any ideas?

Best regards

IsakssonMi

Sure, one way is to use ADO and make a connection to an existing Access Database and populate it from NAV.

Use these two automations:
'Microsoft ActiveX Data Objects 2.6 Library'.Connection (=> Variable ADOConnection)
'Microsoft ActiveX Data Objects 2.6 Library'.Recordset (=> Variable ADORecordset)

And a text-variable: query (TEXT_1024)

build a query:
query := 'insert into mytable (field1, field2) values (''' + rec.name + ''', ''' + rec.amount + '''')';

CREATE(ADOConnection);
CREATE(ADORecordset);

ADOConnection.ConnectionString:=<your_connection_string>;
ADOConnection.Open;
ADORecordset.CursorType:=3;
ADORecordset.Open(query, ADOConnection);

CLEAR(ADOConnection);

Something like that...just a suggestion, haven't test the code.

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]

themave

you can use the odbc driver and bring the data into access or excel.

I use Access to do a lot of work on Navision data,

I have a link to the Navision table in Access and in Access several querries that pull the data into Separate Access tables. then I do all my work on the Access tables, it works very fast even on large files.

Dries

Hi,


In case you are using Navision SQL Server Database then I would advice you to use SSIS instead of using Navision dataports.

SSIS is much quicker and you can create a package and put it in a SQL Job which you can schedule at any time.

If you insist using the export from Navision directly then I won't go for a dataport but I will use a report instead.


Regards,
Dries.

robsmit

The Mave is right.
Skip difficult stories just use C/ODBC or N/ODBC driver.
Then just take any application capable of connecting to that.
Acces, Excel, Visual Foxpro, hundreds more

murtazakhan

HI Mave,
I am good at excel and Navision but an ametuer in pulling data out databases. I'll appreciate if you can elaborate, how can I get the data exported to excel 2010 from Navision 4.0 SP3. I have installed NODBC at the client end but unable to pull the data ](*,) .

Any help would be appreciated.
Regards,

Murtaza.

Savatage

N/ODBC is if you are using the Native Database.
Use ODBC if you're using SQL server

You can read about n/odbc & nav here:
http://www.microsoft.com/en-us/download ... x?id=24432

download & print: w1w1nodbc.pdf