Sometimes you need a “quick look” into navision from an sheet in Excel. For this purposes i have a simple solution to link Excel-Sheets using the “URL-funciton” in Navision Attain 3.01. On pushing a button, Excel will execute a hyperlink to a form in navision and select the record matching to the value of your active excel-cell. I created a “Navlink.XLA” with some easy makros for this job (see examples below). You can get the Hyperlink code by creating an Hyperlink on your desktop and renaming *.url to *.txt (for example in Dos mode) or using “currform.url”. At last you link some buttons in your toolbar to this functions. Please notice that we are using 3.01b Client on a 2.x Database, so you have to check out your own forms and hyperlinks. EXAMPLE:__________________________________________________________ .Const HyperStart = “navision://client/run?servername=YourServer%26” + _ . “database=Navision301%26company=YourCompany%26” . .Const HyperEnd = “servertype=MSSQL” .___________________________________________________________________________ .Public Sub GoToArtikelNr() . ActiveWorkbook.FollowHyperlink (HyperStart + _ . “target=Form%2030%26” + _ . “view=SORTING(Field1)%26” + _ . “position=Field1=0(”_ . + Trim(Selection.Value) + “)%26” + _ . HyperEnd) . .End Sub .___________________________________________________________________________ .Public Sub GoToDebitorNr() . ActiveWorkbook.FollowHyperlink (HyperStart + _ . “target=Form%2021%26” + _ . “view=SORTING(Field1)%26” + _ . “position=Field1=0(” + _ . Trim(Selection.Value) + “)%26” + _ . HyperEnd) . .End Sub .___________________________________________________________________________ .and so on…
I have a stupid question…what does the %26 stands for? This is new territory for me and your suggestion is great. Soon I will have to invest some (lots of) time on ODBC, to link an excel spreedsheet to Navision and this may come in hand. Thanks Joao jpjesus@ibdosportugal.com
This is an absolute beauty of a trick! Many thanks for sharing it! [:p]
Interesting. Although before you invest a lot of time in coding, you may find it beneficial to evaluate JetReports. With the add-on to excel allowing direct excel functions to get data from the Navision database, and also be able to drill from excel cells, I have found this quite a great tool. You can easily and quickly do alot of analysis on navision data. Additionally, you dont even need all users to be Navision users! BUT you will definitely still need the navision C/ODBC purchased in addition to a jet reports license (not too expensive for what it is capable of). If interested, you may go ahead to arrange an online demo (free) with them on www. jetreports.com. Kind regards RBM
The one thing to be aware of with Excel and Jet is data transformation. example 1 - If you have any data that looks like a date, Excel will convert it to a date. Example “jan01” will be converted to Jan 01, 2006. The underlying data will be the julian date for Jan 01, 2006. example 2 - if you have leading zeros, eg “005” it will be converted to the number 5. Leading zeros are dropped. The basic problem is that Jet converts into a “general” formated cell. I have not been able to figure out how to get Jet to output to a “text” field, and NOT transform the Navision data. The above 2 problems has driven me nuts in my Jet extracts, because the vlookup of those fields will fail. And I have to manually scrub the extract for these errors. Gary