BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  TExcelApplication and assignment of Variants          BorlandTalk.com Forum Index -> Delphi OLE Automation View previous topic :: View next topic   Author Message Dan Roy Guest Posted: Thu Aug 03, 2006 11:41 pm    Post subject: TExcelApplication and assignment of Variants I am using Delphi 6 Enterprise with the TExcelApplication component to read/write information to/from Excel. In a particular column on an excel spreadsheet is a float value. In my application, I declare a variable RangeMatrix :variant in my var declaration. In the body of my procedure, after I determine the Excel range, I assign those ranages to the Rangematrix Variant : Type TWPRec = record ..... PMT :string; TC :double; df_date :string; BaseWkStPct: double; BaseWkFinPct :single; LREStWkPct :single; LREFinWkPct :single; BaseStartTC :single; BaseFinishTC :single; LREStartTC :single; LREFinishTC :single; end; Var WPRec :array [1..15000] of TWPRec; Implementation Procedure ReadVarToArray(filename:string); Var .... Rangematrix : Variant; begin .... RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; .... WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); end; Running this section of the application generates a rutime error 216. Whta I do not understand is the assignment to WPREC[keyoff].TC occurs correctly and the printing of the variables to the listbox1 occurs correctly. It is only when I try to close the application that I get the runtime error. Am I doing something wrong in the assignment. If I comment out the assignment and leave the Listbox1 line, then NO runtime erro occurs! Can anyone help me? Dan Back to top Oliver Townshend Guest Posted: Fri Aug 04, 2006 4:04 am    Post subject: Re: TExcelApplication and assignment of Variants Quote: RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; Should you declare rangematrix as a range rather than a variant? Quote: WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); Is rangematrix a range or a cell value? Maybe use value2 instead or .value if it is a cell value? Oliver Townshend Back to top Dan Roy Guest Posted: Fri Aug 04, 2006 5:42 pm    Post subject: Re: TExcelApplication and assignment of Variants "Oliver Townshend" <oliveratzipdotcomdotau> wrote: Quote: RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; Should you declare rangematrix as a range rather than a variant? WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); Is rangematrix a range or a cell value? Maybe use value2 instead or .value if it is a cell value? Oliver Townshend Rangematrix is an array holding data from an Excel spreadsheet. RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; The differences is the normal [C,R] cell referencing, where C is column and R is row, is reversed in this type of Array. Back to top Dan Roy Guest Posted: Fri Aug 04, 2006 5:42 pm    Post subject: Re: TExcelApplication and assignment of Variants "Oliver Townshend" <oliveratzipdotcomdotau> wrote: Quote: RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; Should you declare rangematrix as a range rather than a variant? WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); Is rangematrix a range or a cell value? Maybe use value2 instead or .value if it is a cell value? Oliver Townshend Rangematrix is an array holding data from an Excel spreadsheet. RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; The differences is the normal [C,R] cell referencing, where C is column and R is row, is reversed in this type of Array. Back to top Dan Roy Guest Posted: Fri Aug 04, 2006 6:05 pm    Post subject: Re: TExcelApplication and assignment of Variants "Oliver Townshend" <oliveratzipdotcomdotau> wrote: Quote: RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; Should you declare rangematrix as a range rather than a variant? WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); Is rangematrix a range or a cell value? Maybe use value2 instead or .value if it is a cell value? Oliver Townshend Rangematrix is an array holding data from an Excel spreadsheet. RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; The differences is the normal [C,R] cell referencing, where C is column and R is row, is reversed in this type of Array. What I think is happening is that the Type for the Record does not allow complex types of data. So, strings and integers are OK but doubles are not. I have spent lots of time on the internet trying to find examples of the Record Type for examples but have only found a few, simple ones. Back to top Dan Roy Guest Posted: Fri Aug 04, 2006 6:21 pm    Post subject: Re: TExcelApplication and assignment of Variants "Dan Roy" <daniel.g.roy (AT) baesystems (DOT) com> wrote: Quote: "Oliver Townshend" <oliveratzipdotcomdotau> wrote: RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; Should you declare rangematrix as a range rather than a variant? WPRec[keyoffset].TC := StrToFloat(VarToWideStr(rangematrix[k,13])); // reading column 13 of excel spreadsheet, converting to a float andassigning to field TC in the WPREC structure at index keyoffset. listbox1.items.add(floattostr(WPREC[keyoffset].TC) + ' ' + VarToWideStr(rangematrix[k,13])); Is rangematrix a range or a cell value? Maybe use value2 instead or .value if it is a cell value? Oliver Townshend Rangematrix is an array holding data from an Excel spreadsheet. RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[x,y]].Value; The differences is the normal [C,R] cell referencing, where C is column and R is row, is reversed in this type of Array. What I think is happening is that the Type for the Record does not allow complex types of data. So, strings and integers are OK but doubles are not. I have spent lots of time on the internet trying to find examples of the Record Type for examples but have only found a few, simple ones. I have been experimenting with TVarType ( Delphi Help). I appears that you convert variants to output types by using the VarAsType function (Delphi Help). listbox1.items.add(' b ' + floattostr(VarAsType((rangematrix[k,13]),$0005))); This takes the variant output, converts it to a double, and then Floattostr converts to a string so I can observe it in a listbox. Again, this works perfectly but when I go to close the applciation, there is a runtime error 216. I have isolated the runtime error to this statement. Can you provide some clue as to why this should be happening? Dan Back to top Chris Cheney Guest Posted: Fri Aug 04, 2006 7:56 pm    Post subject: Re: TExcelApplication and assignment of Variants "Dan Roy" <daniel.g.roy (AT) baesystems (DOT) com> wrote in news:44d349d2$1 (AT) newsgroups (DOT) borland.com: Quote: I have been experimenting with TVarType ( Delphi Help). I appears that you convert variants to output types by using the VarAsType function (Delphi Help). Not so. VarAsType converts a variant that contains a value of one type to another variant containing a value of another type: see the Delphi help for VarAsType. If you wish to convert a variant to a string, it is probably simplest to use VarToStr or simply to cast as a string. Quote: listbox1.items.add(' b ' + floattostr(VarAsType((rangematrix[k,13]),$0005))); e.g. listbox1.items.add(' b ' + VarToStr(rangematrix[k, 13])); and then debug by breaking this down further, e.g. var V: Variant; S: String; .... V := rangematrix[k, 13]; S := VarToString(V); S := ' b ' + S; listbox.items.add(S); I will make a stab in the dark and suggest that the 216 might be because the Excel application has closed before Delphi thinks it should have done. HTH Back to top Dan Roy Guest Posted: Mon Aug 07, 2006 11:51 pm    Post subject: Re: TExcelApplication and assignment of Variants Chris, The "value" in rangematrix[k,13] is either a floating point number or 0. I notice when looking at the VARTo??? procedures that there is no VarToFloat procedure. I have experimented with StrToFloat(VarToStr(Rangematrix[k,13])) and am not successful in having this value read without resulting in a runtime error when the application is closed. I have experimented in defining the type of the result in my record definition to be String, Single, Double and Extended and am still having the same problems relative to the Runtime Error 216 appearing. The only time I get a runtime error is when I am reading rangematrix[k,13] , assigning rangematrix[k,13] to a field in my record definition and writing a vlaue to a listbox which uses the rangematrix[k,13] referrence. When I comment out all references to rangematrix{k,13], I do not get any error messages! What is amazing is that the value in my record definition for each and every record is accurate as compared to the input file. There is no variance. I do not believe that I am getting a 216 runtime error due to not closing or freeing objects in my code for it only occurs when I access rangematrix [k,13]. Can you suggest any other avenues I should/could examine. Could it be a function of how I have the IDE configured? Thanks Dan Chris Cheney <cjc1@nospam%ucs.cam.ac.uk%no%spam%please> wrote: Quote: "Dan Roy" <daniel.g.roy (AT) baesystems (DOT) com> wrote in news:44d349d2$1 (AT) newsgroups (DOT) borland.com: I have been experimenting with TVarType ( Delphi Help). I appears that you convert variants to output types by using the VarAsType function (Delphi Help). Not so. VarAsType converts a variant that contains a value of one type to another variant containing a value of another type: see the Delphi help for VarAsType. If you wish to convert a variant to a string, it is probably simplest to use VarToStr or simply to cast as a string. listbox1.items.add(' b ' + floattostr(VarAsType((rangematrix[k,13]),$0005))); e.g. listbox1.items.add(' b ' + VarToStr(rangematrix[k, 13])); and then debug by breaking this down further, e.g. var V: Variant; S: String; ... V := rangematrix[k, 13]; S := VarToString(V); S := ' b ' + S; listbox.items.add(S); I will make a stab in the dark and suggest that the 216 might be because the Excel application has closed before Delphi thinks it should have done. HTH Back to top Chris Cheney Guest Posted: Tue Aug 08, 2006 3:42 am    Post subject: Re: TExcelApplication and assignment of Variants "Dan Roy" <daniel.g.roy (AT) baesystems (DOT) com> wrote in news:44d78ba2$1 (AT) newsgroups (DOT) borland.com: Quote: The "value" in rangematrix[k,13] is either a floating point number or 0. I notice when looking at the VARTo??? procedures that there is no VarToFloat procedure. I have experimented with StrToFloat(VarToStr(Rangematrix[k,13])) and am not successful in having this value read without resulting in a runtime error when the application is closed. I could not see why you are fiddling with variant types when you appeared to need a string value to put into the listbox (your first post). Quote: I have experimented in defining the type of the result in my record definition to be String, Single, Double and Extended and am still having the same problems relative to the Runtime Error 216 appearing. The only time I get a runtime error is when I am reading rangematrix[k,13] , assigning rangematrix[k,13] to a field in my record definition and writing a vlaue to a listbox which uses the rangematrix[k,13] referrence. When I comment out all references to rangematrix{k,13], I do not get any error messages! What is amazing is that the value in my record definition for each and every record is accurate as compared to the input file. There is no variance. I do not believe that I am getting a 216 runtime error due to not closing or freeing objects in my code for it only occurs when I access rangematrix [k,13]. Can you suggest any other avenues I should/could examine. Could it be a function of how I have the IDE configured? 216 is, I believe, access violation. Your rangematrix is a variant - this is 16 bytes in size. Excel is assigning a matrix of variants to your rangematrix variant value - i.e. your variant ends up pointing to an area of store provided by Excel. You need to ensure that it gets freed cleanly and that you don't have any dangling references to it after it gets freed. 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 Page 1 of 1   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.