BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Reading Excel Drop Down, not like Check Box Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi OLE Automation View previous topic :: View next topic   Author Message mIKE Guest Posted: Thu Jul 20, 2006 1:04 am    Post subject: Reading Excel Drop Down, not like Check Box After receiving some help to read the state of an Excel Check Box in a D5 program, I now find that similar logic does not work for an Excel Drop Down. Both the Check Box and the Drop Down are from the Form Tools menu (NOT the ActiveX controls). The Drop Down is called "Combo Box" on the palette, but when you put one on the spreadsheet, the Name Box identifies it like "Drop Down 46". (Note that ActiveX controls, by contrast, are named without the spaces, like "CheckBox2" and "ComboBox46".) (Note that Deborah Pate's site does not have any code dealing with reading these controls that are from the Form Tools menu.) Here's the code that works well for reading a Check Box: var CB: Excel2000.Checkbox; CB := ExcelWorksheet1.CheckBoxes('Check Box 2') as Excel2000.Checkbox; if (CB.Value = 1) then // True And here's what I'm trying to use for the Drop Down: var DD: Excel2000.Dropdown; DD := ExcelWorksheet1.Dropdowns('Drop Down 46') as Excel2000.Dropdown; // The above produce no run-time errors. The following statment produces the subsequent error. if DD.Text = '' then exit; // only using this construct for testing run-time access to Text. EOleException with the message 'Unable to get the Text property of the DropDown class' I've also tried reading DD.LinkedCell, but it's blank even when the drop-down shows a selected value. Though both DD.LinkedCell and DD.Text appear as inaccessible values in the debugger, I can read DD.LinkedCell in code and get an empty string, whereas reading DD.Text produces the error. The contents of the actual linked cell is non-blank, as is the display in the cell that contains the Drop Down control. Macro security is turned OFF in this spreadsheet before it was opened for this session. The result is the same whether the DropDown is Excel97.DropDown and Excel2000.DropDown. Maybe some purchasable component has solved this riddle, but I'd prefer to avoid yet another component if some simple code changes can finish the task here. I do have JEDI installed, but I haven't looked for useful component there. mIKE Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 3:58 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: I've also tried reading DD.LinkedCell, but it's blank even when the drop-down shows a selected value. Though both DD.LinkedCell and DD.Text appear as inaccessible values in the debugger, I can read DD.LinkedCell in code and get an empty string, whereas reading DD.Text produces the error. The contents of the actual linked cell is non-blank, as is the display in the cell that contains the Drop Down control. Macro security is turned OFF in this spreadsheet before it was opened for this session. LinkedCell is a property on the control that you set, and when the control has text entered, the linkedcell gets it too. You should try setting this value, and then read the relevant cell. Oliver Townshend Back to top mIKE Guest Posted: Thu Jul 20, 2006 5:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: I've also tried reading DD.LinkedCell, but it's blank . LinkedCell is a property on the control that you set, and when the control has text entered, the linkedcell gets it too. You should try setting this value, and then read the relevant cell. I'm still confused. The user has these spreadsheets that have drop downs on them. The drop down is configured to get its values from a range of cells on a different page. This is just like we do when we have a separate lookup table that feeds the available values to a ComboBox. I do NOT want to set a value in a cell and have it copied to the LinkedCell. I only tried to reference the LinkedCell property because I thought that maybe the reference worked in the reverse direction from what you described. So, maybe the LinkedCell property is not useful for what I'm tyring to do. I'm not sure that I have understood you. All I want to do is read the contents of the control. In this case, the control gets its value from some other range of cells, and I was guessing that a specific value in the control was tied to a particular cell in that range, and that the relationship between the two was indicated by LinkedCell, but your description makes it sound like my understanding was not correct. So, if all I want to do is read the value in the control, what do I have to do? Does LinkedCell play any role? In the interim, I looked at DB.Value (Integer) to see if it varies with the position of the item selected in the dropdown. If it did, then maybe I could have used it relative to the top of the defined range of cells that the control uses to get its displayed values. Unfortunately, no matter which drop down item is selected, DD.Value = 0. mIKE Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 6:01 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: So, if all I want to do is read the value in the control, what do I have to do? Does LinkedCell play any role? When you use LinkedCell, the cell in question gets updated when the control has a value entered. Then you can just grab the value from the cell. That's what I do. Quote: In the interim, I looked at DB.Value (Integer) to see if it varies with the position of the item selected in the dropdown. If it did, then maybe I could have used it relative to the top of the defined range of cells that the control uses to get its displayed values. Unfortunately, no matter which drop down item is selected, DD.Value = 0. Try the DB.Text property, or see what you can make of the following VBA code which I use to initialize comboboxes in my macros: For i = 1 To Profile.OLEObjects.Count If InStr(Profile.OLEObjects(i).ProgId, "ComboBox") <> 0 Then Profile.OLEObjects(i).Object.ListIndex = 0 End If Next i Oliver Townshend Back to top mIKE Guest Posted: Thu Jul 20, 2006 7:18 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: So, if all I want to do is read the value in the control, what do I have to do? Does LinkedCell play any role? When you use LinkedCell, the cell in question gets updated when the control has a value entered. Then you can just grab the value from the cell. That's what I do. If that's something I can do at run-time for my Delphi 5 program, then I can try it. I receive these sheets and I don't control how they work. I just have to read them. I can modify them using code, but it is not feasible to manually go back and re-configure all of the drop downs in all of the copies of the sheets that I'll receive. To test this....After the DD is created in code, then I tried setting DD.LinkedCell to '[9, 33]'. After that, I see that the cell on the spreadsheet hasn't changed and my reading of that cell in Delphi code doesn't show anything. By contrast, when I go into Excel and right-click | Format Control and set Cell link to I33, cell I33 is updated to show the ordinal position of the selected value, relative to the top of the specified range of cells where the drop down gets its values. IOW, it doesn't mirror the text contents; Instead, it puts an Integer in the LinkedCell. Is there a command to refresh the dropdown in such a way to trigger the updating of the LinkedCell while my Delphi 5 program is running? Alternatively, I could write some code to set the Cell link value in the spreadsheet to the same row but column plus 50 (that is never otherwise used), then read the value there. So, if I cannot get the refresh to happen when I set DD.LinkedCell, this other scheme would permit me to set the Cell link value in the spreadsheet in a manner that would update the cell. Maybe this is the wrong forum for this, because it sounds like a job for VBA. I'd need to traverse all the Drop Down components and insert the cell links as current-row, current-column-plus-50. Is there a way to write a Delphi program to insert the VBA (macro?) code in the spreadsheet and run it from the Delpi program? I'll recheck Deborah Pate's site for this code. Quote: Try the DB.Text property, or see what you can make of the following VBA code which I use to initialize comboboxes in my macros: Per my original message, for some reason the DD.Text property cannot be accessed in my program. //The following statement //produces the subsequent error. if DD.Text = '' then exit; EOleException with the message 'Unable to get the Text property of the DropDown class' That is a completely separate issue. Do you have any idea why this happens? I realize that when I place a Delphi 5 Excel Worksheet component on the form, it adds Excel97 to the Uses clause. I added Excel2000 manually when that was the only way I could get the checkbox reading code to compile. (I just guessed that this would resolve the compile time message.) Any theories about why I cannot access the DD.Text property? Quote: For i = 1 To Profile.OLEObjects.Count If InStr(Profile.OLEObjects(i).ProgId, "ComboBox") <> 0 Then Profile.OLEObjects(i).Object.ListIndex = 0 End If Next i I've been away from VB/VBA for quite a while, but the structure of the code is simple. I just have to translate the nouns to names that are used in the corresponding Delphi 5 components. mIKE Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: BTW, where do you get information like this? Sorry, its from a Macro I wrote 3 years ago in Excel. I probably got it by searching Google or by reading the VBA help files that come with Excel, eg "C:\Program Files\Microsoft Office\OFFICE11\1033\VBAWD10.CHM" (which is the Word Help file, but the Excel one is in the same folder). Oliver Townshend Back to top mIKE Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: Ah gotcha. Oh well, my assistance hasn't been very helpful has it? Sorry. I used the ActiveX controls. I thought you were creating the spreadsheet through automation, but you're not, so my comments aren't relevant. Via Google, using the clue: excel set cell link, I found the following code in microsoft.public.excel.programming: For Each bx In ActiveSheet.DropDowns bx.LinkedCell = bx.TopLeftCell.Address(external:=True) Next This sets the Cell link to the cell that is under the top left corner of the drop down control. This still has the limitation of putting the Integer value of the ordinal position of the selected item in the range of referenced cells, but from that, you'd think I could derive the contents of that lookup cell. I'd hope to do this using the parameters of the Drop Down control to find the top item in the list, but if I have to I can hard-code the top cell coordinates until I find the elegant solution. I also needed to surround this in code to loop through all of the sheets in the workbook, so I don't have to execute the macro separately for each sheet after making it the active sheet: Dim WS_Count As Integer Dim I As Integer WS_Count = ActiveWorkbook.Worksheets.Count For I = 1 To WS_Count For Each bx In ActiveWorkbook.Worksheets(I).DropDowns bx.LinkedCell = bx.TopLeftCell.Address(external:=True) Next Next I If I can figure out how to find the relative position of the cell indicated by the integer in the LinkedCell, I can make my Delphi programming even simpler. Maybe I can access some sub-property of bx.InputRange to get the text value I eventually need. Then I can see about writing Delphi code to insert that code into each spreadsheet when I open it, then run that macro before reading to get the contents of the drop downs. Thanks for helping me walk/talk through this problem. mIKE Back to top mIKE Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: Are you saying that I should use the ActiveX controls instead? Well, I don't have that option because the spreadsheets already exist. Ah gotcha. Oh well, my assistance hasn't been very helpful has it? Sorry. I used the ActiveX controls. Being an OLE novice, I'm not even sure what you mean by this. Care to explain further? I thought you were creating the spreadsheet through automation, but you're not, so my comments aren't relevant. In other situations I've created spreadsheets through automation, and it's all gone quite well because the cells only contained, text, numbers, and formulas. So, thanks for trying. Maybe there's still a way to insert and run some VBA code in a macro to create the Cell links (DD.LinkedCell) from within my Delphi program. Then I could just read the integer from the LinkedCell and then find the text in the corresponding cell that is identified withing the Input range (right-click | Format Control | Input range). If I cannot accomplish this (using code), then I'd have to tell the client to spend a LOT of time on many instances of the spreadsheet, manually re-configuring the Drop Downs to specify the Cell link. Ouch! mIKE Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: Thanks for helping me walk/talk through this problem. Good luck. I think you're seeing all of the reasons why I used ActiveX. Oliver Townshend Back to top mIKE Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: BTW, where do you get information like this? I probably got it by searching Google or by reading the VBA help files that come with Excel, eg "C:\Program Files\Microsoft Office\OFFICE11 \1033\VBAWD10.CHM" (which is the Word Help file, but the Excel one is in the same folder). Thanks for that. I've now found the Excel help file. I don't know if you noticed that I also posted a message before the "BTW" message. Any thoughts on that one? mIKE Back to top mIKE Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: LinkedCell is a property on the control that you set, and when the control has text entered, the linkedcell gets it too. You should try setting this value, and then read the relevant cell. BTW, where do you get information like this? When I examine the Delphi 5 help for the various Excel OLE components, they all just show generic help for TOleServer. I could probably make a lot more progress toward the solution if I knew of a printed resource for the actual properties and events that appear when using the Delphi 5 Excel automation components. mIKE Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: The Combo Box control on the Form Tools menu/toolbar is not the ActiveX control (that are on the Control Toobox toolbar). Though the contol on the tool-bar is called Combo Box, placing it on the spreadsheet "names" it like "Drop Down 46". Are you saying that I should use the ActiveX controls instead? Well, I don't have that option because the spreadsheets already exist. Ah gotcha. Oh well, my assistance hasn't been very helpful has it? Sorry. I used the ActiveX controls. Quote: Being an OLE novice, I'm not even sure what you mean by this. Care to explain further? I thought you were creating the spreadsheet through automation, but you're not, so my comments aren't relevant. Oliver Townshend Back to top Oliver Townshend Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Quote: Any thoughts on that one? I think either the OLEObjects or the LinkedCell are your solutions. Especially if you want to use Excel 97. If you are using Excel 97, use ComboBoxes, don't use other sorts of List Boxes because they crash. Personally I automate templates with my spreadsheet already set up, rather than creating them through OLE. Oliver Townshend Back to top mIKE Guest Posted: Thu Jul 20, 2006 8:12 am    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: I think either the OLEObjects or the LinkedCell are your solutions. Especially if you want to use Excel 97. If you are using Excel 97, use ComboBoxes, don't use other sorts of List Boxes because they crash. Well, I only "use" Excel97 in that the dropping a D5 Excel component on the spreadsheet puts Excel97 in the Uses clause. My code specifically refers to Excel2000 components. Though my practices have worked fine for reading Check Boxes, maybe there's something different about Drop Downs that would work better if I didn't drop components on the Delphi form, and instead did it all in code. The Combo Box control on the Form Tools menu/toolbar is not the ActiveX control (that are on the Control Toobox toolbar). Though the contol on the tool-bar is called Combo Box, placing it on the spreadsheet "names" it like "Drop Down 46". Are you saying that I should use the ActiveX controls instead? Well, I don't have that option because the spreadsheets already exist. Quote: Personally I automate templates with my spreadsheet already set up, rather than creating them through OLE. Being an OLE novice, I'm not even sure what you mean by this. Care to explain further? mIKE Back to top mIKE Guest Posted: Fri Jul 21, 2006 7:21 pm    Post subject: Re: Reading Excel Drop Down, not like Check Box Oliver: Quote: Good luck. I think you're seeing all of the reasons why I used ActiveX. As it turns out, I was able to get help on the excel programming newsgroup, to complete my macro that not only assignes the LinkedCell, but then uses the Index (integer) that gets placed there, to find the text string that I really need. FWIW, the VBA code for the macro is this: Dim WS_Count As Integer Dim I, idx As Integer Dim Val As String ' Set WS_Count equal to the number of ' worksheets in the active workbook. WS_Count = ActiveWorkbook.Worksheets.Count For I = 1 To WS_Count For Each bx In ActiveWorkbook.Worksheets(I).DropDowns bx.LinkedCell = bx.TopLeftCell.Address(external:=True) idx = Range(bx.LinkedCell).Value If idx Then Val = Range(bx.ListFillRange)(idx, 1) ' MsgBox "idx = " & idx & " " & bx.Name & " = " & Val ' Then write Val out to some cell like ActiveRow, ActiveColumn + 20 End If Next Next I After that is run, then my Delphi program can easily read the text in that cell, though it originated in a Drop Down from the Form Tools menu. mIKE Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi OLE Automation All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.