Options

Importing Data into Excel or Access

andreasnandreasn Member Posts: 4
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

Comments

  • Options
    IsakssonMiIsakssonMi Member Posts: 77
    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.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    [Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    themavethemave Member Posts: 1,058
    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.
  • Options
    DriesDries Member Posts: 29
    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.
  • Options
    robsmitrobsmit Member Posts: 12
    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
  • Options
    murtazakhanmurtazakhan Member Posts: 11
    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.
  • Options
    SavatageSavatage Member Posts: 7,142
    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
Sign In or Register to comment.