Hi, Does anyone know firstly how to reference drop down box controls in Excel, and secondly how I can assign values to this drop down list. Any help would be much appreciated. Thanks Ryan
Inside Excel? Mark the cell you want the drop down to appear in, choose Data, Validation. The rest is straightforward. Lars Strøm Valsted ------------------------- Why can’t programmers tell the difference between Christmas and Halloween? Because OCT(31) = DEC(25)
I already have the controls in a template in Excel, and they seem to ‘float’ over the cells (they are not tied to particular cells). I can name the drop down control, but do not know how to reference it from within Navision using the Automation object.
I think your problem is that the dropdowns are floating. You need to tie them to a single cell, which you can do as I described it. I have never seen them floating…hmm, strange… Lars Strøm Valsted ------------------------- Why can’t programmers tell the difference between Christmas and Halloween? Because OCT(31) = DEC(25)
I’m not sure how these controls have actually been created - the template is from a client, but they definately seem to be placed above the worksheet, as I can still access the cells behind the controls. I have tried to attach a spreadsheet with some Check box controls that I also wish to reference and control from Navision, but the file type is not allowed. Any ideas on what is going on?
The client has set them up as form fields on the excel spreadsheet. Go to View->Toolbars->Forms. You can select drop down lists and checkboxes etc from here. You can link them to a cell by right clicking the control and selecting Format Control. You can look at the VB code by clicking the “Edit code” icon in the list where the check boxes, drop down lists etc are… You can then look at the VB code. I ususally write the automation in VB and save it as an OCX. Hope this helps.
quote:
I ususally write the automation in VB and save it as an OCX.
Sounds interesting. Could you please give me some details on this? You create an OCX and integrate it into Navision, instead of writing the automation code directly in C/AL? – Heinz Herbeck Waagner-Biro AG, Vienna, Austria
Yeah it is simple… Start up Visual Basic (i use VB6)… and select ActiveX Control…Don’t worry about the user control form…you probably wont need this… Anyway…Click on UserControl1 and then go to the window behind where we can write the code. Here you can write many different functions as you would normally. If you want to call the mfrom Navision they need to be Public! To use excel objects you would click on Project->References and then select Microsoft Excel Objects 10.0 or whatever… when you have finished writing the code and are happy that it works then Click File->Make Project1 (or whatever you’ve saved the file as).OCX. Then save where you want the OCX to go. Then you have to register the OCX on the Navision Client. You can then assign a varibale of OCX type as you would normally in Navision…you should be able to see all your properties and methods in the symbol menu… Voila… A piece of cake…apart from any client which needs to run code associated with this OCX must have the OCX registered on it!
Thanks for the info! Actually, I did the same thing with a COM-DLL instead of an OCX, creating a wrapper class for Easy-Archive. When I read your previous posting, I thought you were relating to something specific to Excel, like e.g. encapsulating Excel VB macros inside an OCX. A little technical detail: Is there a significant difference between using an OCX or a COM-DLL/EXE server inside Navision? As I understand it, you have to register each one of them, then create automation variables, and then use the methods and properties, don’t you? The one major advantage of OCXs, being able to place them on a form as controls, is unavailable in Navision (at least in 2.60). – Heinz Herbeck Waagner-Biro AG, Vienna, Austria
To Akers CC Everyone else I have recently had to incorporate a Word automation solution for a client. The client has certain documents that they require data to be populated, document advanced properties to be set, documents to be combined (many documents combined to make up a single complete document), logos to be placed on the document depending on a field in Attain & tables on the template to be populated according to values in a recordset (in Attain). We found the best way to tackle this “beast” (word automation), was to write macros on the template documents and call them from Attain. eg. Attain code:- WrdApp.Run(‘TableDelete’); Word document template macro:- Function TableDelete() ActiveDocument.Tables(1).Delete End Function This has proven to be very stable and a good solution. I must say, I cant ever remember saying the words “very stable” together when talking about automation so this has proven to be a first. So my question to Akers is, have you looked at this approach? I ask because I wondered if you’ve tried this solution, but found it problematic?!?
I personally haven’t tried this approach as i am working with Financials and the client is using office 97. We found there to be loads of problems if macro’s were on the document templates which we were opening. Also running macro’s was out of the security scope of our client.