Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 4:38 PM, GS wrote:
"...most development languages use DateSerial" Well, let's say most have a library available for the developer _to_ use. :) Actually, for VB[A] it's called a runtime.dll which is, as noted, the dev app's library. The developer has no control over the functions or methods the lib exposes, though. MS, of course, has a problem in that they think 1900 was a leap year so are off by one compared to those who don't (correctly). I don't know which way the C library work or POSIX or whether there's an interpretation on how should work. Most langs use DateSerial for date values since this has more-or-less been adopted as a 'global' standard so 'regions' will capture the real value in local formats. I'm thinking, though, that terminology and familiarity with processes has played a role in any misunderstandings that prevailed here. Entering 05/07/2018 into a cell in Excel (or any other spreadsheet) is raw data not text. Entering that same data into a grid control is both raw data and text. Don't know how your MatLab takes values but your dialog here suggests it accepts string values that it uses to process whatever, OR interprets/treats entered values as string data. Does this mean what gets entered is wrapped in ""? (You could enlighten me further on this, if you would please.) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
El viernes, 4 de mayo de 2018, 2:31:09 (UTC-5), escribió:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or MM-DD-YY? How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of April? Jan x="4-5-18" ?format(x,"mm/dd/yyyy") 'Return 05/04/2018 ?format(x,"Long date") 'Return viernes, 4 de mayo de 2018 |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 11:19 PM, GS wrote:
On 5/7/2018 4:38 PM, GS wrote: "...most development languages use DateSerial" Well, let's say most have a library available for the developer _to_ use.* :) Actually, for VB[A] it's called a runtime.dll which is, as noted, the dev app's library. The developer has no control over the functions or methods the lib exposes, though. For the most part it's just a wrapper around the C RTL and you can call C RTL directly if wish (or roll your own)... MS, of course, has a problem in that they think 1900 was a leap year so are off by one compared to those who don't (correctly).* I don't know which way the C library work or POSIX or whether there's an interpretation on how should work. Most langs use DateSerial for date values since this has more-or-less been adopted as a 'global' standard so 'regions' will capture the real value in local formats. I still say "the language" does no such thing; the language may or may not include the library but it's entirely up to the developer to use whatever package or encoding they choose. I'm thinking, though, that terminology and familiarity with processes has played a role in any misunderstandings that prevailed here. Entering 05/07/2018 into a cell in Excel (or any other spreadsheet) is raw data not text. Entering that same data into a grid control is both raw data and text. It's ASCII characters typed or pasted in from clipboard or whatever other source, that's "text". How Excel interprets it is parsing that text; spreadsheets have their rules which are quite complex as compared to procedural languages; that's their advantage as being a much "friendlier" coding paradigm for users that are not traditional programmers. Don't know how your MatLab takes values but your dialog here suggests it accepts string values that it uses to process whatever, OR interprets/treats entered values as string data. Does this mean what gets entered is wrapped in ""? (You could enlighten me further on this, if you would please.) Depends on the source, and how one is using it...at the command line it's much like working at the Immediate window in Excel VBA; one simply writes executable statements in Matlab syntax (which is very much Fortran/VB-like in form) and strings those together to achieve a goal. Of course, for applications or repeatable tasks, one writes code ("m-files") for functions (local scope) and/or scripts (global) and strings them together just like writing a VB app excepting one doesn't have the container of the spreadsheet; one handles all data as either native arrays or higher-level constructs of structs, tables, etc., etc., .... ML is different from VB in that it is weakly typed in that there are no variable declaration statements; the double is the default type, everything else is a cast() to some other type. As far as strings, the original base language treated them like F77 Fortran--as arrays of char(). Cell arrays were introduced which operate much like a Variant in VB in holding mixed types and with it special syntax for the cellstr() string; fairly recently a new strings() class has been introduced which looks and acts much more like a VB string. As far as quoted strings; they're recognized by the '%q' C-like format string in context of fscanf() and friends or in other higher-level routines that are part of the base package of Matlab such as readtable() or textscan() or csvread(). The base Matlab package consists of hundreds of these functions, the most fundamental of which are compiled code, the high-level ones are often m-files themselves and distributed with the product as source and are interpreted no differently than user-written functions. -- |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
Thanks for the details, ..very interesting stuff!
I still say "the language" does no such thing; the language may or may not include the library but it's entirely up to the developer to use whatever package or encoding they choose. Well I'm going to flatly assert that you're wrong based on the 5/6 languages I've studied/used because all of them returned a DateSerial property for values stored in Date data Types. (I'm not just a VB[A] guy)<g How Excel interprets date values has nothing to do with VB[A]; - it just happens to also be a function of how spreadsheets work. (I'm also not just an Excel guy)<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 6:45 AM, GS wrote:
Thanks for the details, ..very interesting stuff! I still say "the language" does no such thing; the language may or may not include the library but it's entirely up to the developer to use whatever package or encoding they choose. Well I'm going to flatly assert that you're wrong based on the 5/6 languages I've studied/used because all of them returned a DateSerial property for values stored in Date data Types. (I'm not just a VB[A] guy)<g Of course the native or companion library will return its type; it can hardly do anything else. :) But...that's not of which I speak here. You're conflating different things...that there _may_ be an intrinsic Date data type has no bearing upon whether the developer does/does not use it for any give application or feature within an application built in the language. Of course, if the language either natively or with a companion library supports such a thing, it only makes sense to use it. Returning to the subject here that the OP has time stamps as text strings from a data acq app, it's quite possible all it does is read a real-time clock and never does anything except record that as a string and so never has a time-type variable of whatever ilk the development language may support. Fortran doesn't have a native date type at all; in Matlab, the original datenum is just a double and while is a serial number doesn't use the same epoch as does Excel (there are supplied translation routines). With the introduction of OOP features into Matlab, Mathworks has begun implementing new classes, the datetime() is one which is an enhancement of datenum implemented as an opaque object with methods and properties that provide similar functionality as you're used to with Excel operations on its Date. There's another whole thing called a timetable() that is a rectangular collection of things associated with a specific time sequence that is intended for such things as what it appears the OP has here. How Excel interprets date values has nothing to do with VB[A]; - it just happens to also be a function of how spreadsheets work. (I'm also not just an Excel guy)<g Again, POV...spreadsheets work how they do because that's the way the developers used the underlying programming language to make them work! That they used the base features of that language as model or directly is hardly surprising and then build upon those to provide for the specific desired functionality. How MS chose to interpret input dates in Excel has to do with their penchant for thinking the world should rotate around MS as much as anything else; that the system has no way other than global change to the locale setting for importing dates from alternate sources is symptomatic. OTOH, Matlab has the facility to read from whatever input source in a user-defined format for any particular variable including conflicting definitions for two adjacent columns in the same data file if that were to be the way the data had been produced. AFAICT, that's not possible in Excel without either first preprocessing the data to produce either a system-locale consistent format, an unambiguous format recognizable by Excel or to force to not be interpreted as dates at all and do the conversion internally. In my view, "that's just rude!" and negates much of the point of having a general programming language if one can't truly be general; it's the computer that should bow to the user, not the other way 'round. $0.02, imo, ymmv, etc., etc., etc., ... :) To illustrate, an example of what the OP might be encountering as I understand it. The following is cut 'n paste from the Matlab command window to answer more about what interactive input "looks like". No, one doesn't have to quote strings as input nor is it a string-processing language like REXX (altho to input to a string variable that text would have to be quoted, of course, to prevent the interpreter from trying to execute whatever the text was as code). type dates.txt % a short data file contains two ambiguous dates 5/6/2018, 5/6/2018 1) Read as m/d/yyyy for both... readtable('dates.txt','readvariablenames',0,'forma t',repmat('%{M/d/yyyy}D',1,2)) ans = 1×2 table Var1 Var2 ________ ________ 5/6/2018 5/6/2018 month(ans{1,:}) % see what we got... ans = 5 5 2) now presume one is from that other data acq system we can't change... readtable('dates.txt','readvariablenames',0,'forma t',['%{M/d/yyyy}D','%{d/M/yyyy}D']) ans = 1×2 table Var1 Var2 ________ ________ 5/6/2018 5/6/2018 month(ans{1,:}) % see what we got... ans = 5 6 Indeed, we got the two imported correctly even though they have different encodings and are visually ambiguous--much better scenario than having to try to guess and figure out after the fact what Excel did behind our back. By default, Matlab keeps the input format for display format so both still visually look the same; we can fix that, too-- d2=readtable('dates.txt','readvariablenames',0,'fo rmat',['%{M/d/yyyy}D','%{d/M/yyyy}D']); d=d2.Var2; % retrieve to work on one variable d.Format='M/d/yyyy'; % make formatting consistent for all d2.Var2=d % put back into the table... d2 = 1×2 table Var1 Var2 ________ ________ 5/6/2018 6/5/2018 One can use whatever display format wanted, of course, so can also use d.Format='dMMMyyyy' d = datetime 5Jun2018 And, of course, there also operations on them as one would expect-- d2.Var2-d2.Var1 ans = duration 720:00:00 days(ans) ans = 30 The duration type also has format property for display, default is hours and there are operations for fixed 24-hr days or calendar durations depending on whether it's pure time or calendar time one is interested in, etc., etc., etc., ... Just scratching the surface; if you were interested, there's Octave which is a Matlab workalike--it has _most_ of what Matlab has in base product with some other features that aren't (and vice versa). I've not checked but I presume by now it has similarly functional datetime class. -- |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
Ok, how MatLab handles this isn't relevant; how Excel handles it is all that
matters here! If the target cells are pre-formatted (ergo using a template) to receive their expected data then both columns in your example will display correctly when importing the text. AFAIC, anyone arbitrarily 'dumping' data into a default blank worksheet that's not pre-designed to receive it needs schooling on how to use Excel! (But then, what do I know?) Since most other spreadsheets (at least those that claim to read/write Excel formats) are compatible by attrition of competiveness with Excel, also handle NumberFormats same as Excel does. Numeric date formats are no longer viable because of OS/Local system formats differing as per. People need to stop using them and use textual format where the month is at least "mmm", regardless of which element it occupies. The various spreadsheets will interpret correctly based on DateSerial. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 9:45 AM, dpb wrote:
.... ... How MS chose to interpret input dates in Excel has to do with their penchant for thinking the world should rotate around MS as much as anything else; that the system has no way other than global change to the locale setting for importing dates from alternate sources is symptomatic. Back to the Excel-specific issue; my test that to me looks like it shows that Excel is broke was to 1) Enter 5/6/2017 into A1 and A2 initially formatted as General 2) Observe the Excel converted both to Date as mm/dd/yyyy 3) Apply Custom format of dd/mm/yyyy to A2 4) Observe the display change to 6/5/2018 -- looks good, doesn't it? 5) Enter =A1 and =A2 into B1 and B2 6) Format B1 and B2 as d-mmm 7) Observe both read 6-May!!! If one places focus on B2, one observes that it still contains 5/6/2018 and so all Excel has done is to change the output format; it doesn't "believe" you really mean the data itself is in that format and fix it; it's already past that stage and internally created the serial number for the date as per the system locale setting. That's why I have concern that the OP has a real problem in that what he can display as _appearing_ correct for the two sets of data from the foreign system very well may not actually be right at all. Am I seeing this wrong, somehow? I'm certain that if one knows which cell contains data from which source one can write code to fix it, but afaict there's no way to get both into the spreadsheet correctly without some such machination. This also makes me wonder what happens to existing data if the locale is change altho I've not tried to see... It just looks to me like MS has used too big a club and made too many assumptions regarding input format. Now again, yes, using a non-ambiguous format for the external system would solve the issue; but that's not what OP has...whether he can fix the system itself is unknown; as bad as that situation is, I certainly have seen very many which don't provide that flexibility to the end user. -- |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
1) Enter 5/6/2017 into A1 and A2 initially formatted as General
2) Observe the Excel converted both to Date as mm/dd/yyyy 3) Apply Custom format of dd/mm/yyyy to A2 4) Observe the display change to 6/5/2018 -- looks good, doesn't it? 5) Enter =A1 and =A2 into B1 and B2 6) Format B1 and B2 as d-mmm 7) Observe both read 6-May!!! Of course it does! What were you expecting? If you format all the cells as Text, you'll see that the underlying DateSerial for the value entered is 43226 (May 6, 2018). It doesn't matter how you format it to display because the DateSerial drives the result. My point is *why deliberately use an ambiguous format* to display dates given the issue? This is what my exercise demos! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 1:34 PM, GS wrote:
Ok, how MatLab handles this isn't relevant; how Excel handles it is all that matters here! Trudat; I just included it as thought you found the comparisons of some personal interest if nothing else...it is how I avoid the grief personally by essentially not using spreadsheets. :) If the target cells are pre-formatted (ergo using a template) to receive their expected data then both columns in your example will display correctly when importing the text. AFAIC, anyone arbitrarily 'dumping' data into a default blank worksheet that's not pre-designed to receive it needs schooling on how to use Excel! (But then, what do I know?) Ah! OK, that would answer the followup I posed (I think)...but in my experience, the number of people tasked with performing analyses that have ever had any specific Excel training that would lend them to understanding this point is miniscule at best and, at least ime at the utilities and other places I've consulted could best be approximated as "none". I must admit I'm not certain how one goes about doing that; I tried to "preformat" the cells and Excel wouldn't let me do anything to them until there was something else in the cell already...this is past my level of competence with Excel; "dumping data into a blank sheet" is all I've ever known to do myself! :) Since most other spreadsheets (at least those that claim to read/write Excel formats) are compatible by attrition of competiveness with Excel, also handle NumberFormats same as Excel does. Numeric date formats are no longer viable because of OS/Local system formats differing as per. People need to stop using them and use textual format where the month is at least "mmm", regardless of which element it occupies. The various spreadsheets will interpret correctly based on DateSerial. That's a noble objective; in the real world I've lived in it just "ain't agonna' happen!" and, much like the Fortran world from whence my background arises, there's just too much legacy code and applications out there that replacing them all is cost prohibitive and also not happening. Anyways, if/when you see and care to address the recent follow up I think we have clearly reached the end of the fruitfulness of the discussion...altho I, too, have learned something (and may learn something more) "how Excel works" that will have to keep in mind going forward. -- |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 2:02 PM, GS wrote:
1) Enter 5/6/2017 into A1 and A2 initially formatted as General 2) Observe the Excel converted both to Date as mm/dd/yyyy 3) Apply Custom format of dd/mm/yyyy to A2 4) Observe the display change to 6/5/2018 -- looks good, doesn't it? 5) Enter =A1 and =A2 into B1 and B2 6) Format B1 and B2 as d-mmm 7) Observe both read 6-May!!! Of course it does! What were you expecting? Well, I wasn't absolutely positive which is why I did it! :) My previous thought was that the date format selection actually did do an implicit conversion rather than being just the format of moving fields around. That there's reasons I grok; I was "underneath the impression" as my former colleague Mr. Malaprop was wont to say that one could manage to recast that way; clearly I was wrong and I'm guessing in the subject thread the OP is as well. If you format all the cells as Text, you'll see that the underlying DateSerial for the value entered is 43226 (May 6, 2018). It doesn't matter how you format it to display because the DateSerial drives the result. My point is *why deliberately use an ambiguous format* to display dates given the issue? This is what my exercise demos Well, as the other follow-up comments, sometimes one doesn't have the choice of what input format to use and I was trying to figure out how the OP could solve his problem under those constraints. Sometimes ideal solutions are just not available in the real world. Can you illustrate the idea of the template for the ignorant? :) -- |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
Well, as the other follow-up comments, sometimes one doesn't have the choice of what input format to use and I was trying to figure out how the OP could solve his problem under those constraints. Sometimes ideal solutions are just not available in the real world. Can you illustrate the idea of the template for the ignorant? :) Sure! (Though I don't consider you to be ignorant! You seem to be quite knowledgeable despite some unfamiliarity working in Excel after being so entrenched in MatLab) My solutions, as you know from our collaboration project, are primarily *task oriented* to service repetative processing of data on a regular or period-based schedule. In cases where raw data is imported to a spreadsheet I'll pre-design a template to receive that data in its _expected_ format so it displays in the user's _desired_ format. After all, output data from most apps is usually just plain text. (CSV/TSV or the like) Designing worksheets to receive raw or formatted data correctly is a critical part of my job as an Excel developer. Automating the solution is also another critical part of the job so it enhances user productivity in a consistent reliable manner. Sometimes the data is stored in Excel files which may or may not have formatting, and sometimes the data is stored in a dbase (also with data type formatting). In both these cases I'd use ADODB to load the data into recordsets, then dump it into the worksheet which is already preformatted to receive it. (ergo a pre-designed template) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#52
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
Trudat; I just included it as thought you found the comparisons of some
personal interest if nothing else...it is how I avoid the grief personally by essentially not using spreadsheets Yes indeed! Thanks again for the details; - saves me reading the MatLab userguide! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#53
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 4:04 PM, GS wrote:
.... Sure! (Though I don't consider you to be ignorant! ... Blushes... :) You're too kind! -- |
#54
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 4:04 PM, GS wrote:
.... ... In cases where raw data is imported to a spreadsheet I'll pre-design a template to receive that data in its _expected_ format so it displays in the user's _desired_ format. After all, output data from most apps is usually just plain text. (CSV/TSV or the like) .... Well, I tried the following: 1) created a sheet like the previous example with the two formats m/d/yyyy, d/m/yyyy in A1, B1, respectively containing dummy data. 2) saved the file 3) wrote to those two cells via the ML COM routine the content of the same as previous text file... dates=importdata('dates.txt'); % read the file to a cell string dates=strip(split(dates)); % split at the ',' and clean xlswrite('dates.xls',dates,1,'A1:B1') % and put into the formatted cells Unfortunately, that doesn't seem to override the MS penchant for interpreting by system locale; the format is OK, but the serial date is still based on m/d/yyyy. I don't know much about Excel, but that's about the only way programmatically to push data into it I know; I was able to manually import the file as text with the import tool but it allows you to set the date format on a column-by-column basis; I suppose maybe there's some way to emulate this programmatically? All in all, Excel continues to make me exceedingly glad I don't have to try to use it for engineering work... :) And, yes, I understand this is trying to fix a sorry way to run a railroad...but it seems to be the OPs RR. :) -- |
#55
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 4:04 PM, GS wrote:
... ... In cases where raw data is imported to a spreadsheet I'll pre-design a template to receive that data in its _expected_ format so it displays in the user's _desired_ format. After all, output data from most apps is usually just plain text. (CSV/TSV or the like) ... Well, I tried the following: 1) created a sheet like the previous example with the two formats m/d/yyyy, d/m/yyyy in A1, B1, respectively containing dummy data. 2) saved the file 3) wrote to those two cells via the ML COM routine the content of the same as previous text file... dates=importdata('dates.txt'); % read the file to a cell string dates=strip(split(dates)); % split at the ',' and clean xlswrite('dates.xls',dates,1,'A1:B1') % and put into the formatted cells Unfortunately, that doesn't seem to override the MS penchant for interpreting by system locale; the format is OK, but the serial date is still based on m/d/yyyy. I don't know much about Excel, but that's about the only way programmatically to push data into it I know; I was able to manually import the file as text with the import tool but it allows you to set the date format on a column-by-column basis; I suppose maybe there's some way to emulate this programmatically? Absolutely! See below... All in all, Excel continues to make me exceedingly glad I don't have to try to use it for engineering work... :) And, yes, I understand this is trying to fix a sorry way to run a railroad...but it seems to be the OPs RR. :) Yeah, I don't have much use for Excel's built-in import feature. In solutions where data gets imported from plain text files, the entire process is managed by VB[A] because the import utility will nearly almost always misinterpret the source data. (Or at very least, cannot be trusted to interpret accurately left to its own!) Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control uses cell types so importing text source data is treated according to its target cell. How that cell displays its data is determined by its preset format. (The fpSpread.ocx does not support ConditionalFormatting and so this has to be done programmatically when cell type formats are not preset. In this control, date cells will optionally include a popup calendar so numeric input is not part of the process entering the date value because it knows which day of which month page you selected.) Likewise, cell formats and/or types can be set programmatically before the data is loaded into a worksheet. This is, IMO, a better way to handle dates than Excel offers and so as I'm able to reproduce here what Excel features are missing, so also can I reproduce in Excel programmatically what it lacks. The concept here is this: worksheets can be predesigned to receive data types with specific formats OR be prepared for data types before loading at runtime. Predesigning worksheets in a project workbook is how templates are created. Note, though, that not all sheets in a project workbook need to be predesigned; - just those that receive imported data.:) Even when the source data is generated by another app with known date formats, this can be programmatically managed so the expected format converts to the desired (target) format correctly. This puts you (or your solution) in control, not Excel (or whatever target spreadsheet). Unfortunately, not all non-MS spreadsheets support scripting and so is why I chose a programmable spreadsheet control to duplicate my Excel-based apps as stand-alone Win apps back when MS introduced the Ribbon UI in MSO2007. This enables non MSO users to have the same solutions as MSO users without all the distractions of the Excel UI or its consequent behaviors. (The Excel versions can totally control the UI but not Excel's behaviors.) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#56
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 10:51 PM, GS wrote:
.... Yeah, I don't have much use for Excel's built-in import feature. In solutions where data gets imported from plain text files, the entire process is managed by VB[A] because the import utility will nearly almost always misinterpret the source data. (Or at very least, cannot be trusted to interpret accurately left to its own!) Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control uses cell types so importing text source data is treated according to its target cell. How that cell displays its data is determined by its preset format. (The fpSpread.ocx does not support ConditionalFormatting and so this has to be done programmatically when cell type formats are not preset. In this control, date cells will optionally include a popup calendar so numeric input is not part of the process entering the date value because it knows which day of which month page you selected.) Likewise, cell formats and/or types can be set programmatically before the data is loaded into a worksheet. This is, IMO, a better way to handle dates than Excel offers and so as I'm able to reproduce here what Excel features are missing, so also can I reproduce in Excel programmatically what it lacks. .... OK, so essentially you throw the baby out with the bath water... :) Yeah, w/o additional tools on the Excel end, I'd write "glue" code to do what I illustrated earlier, read the data with a tool I know I can control to import the given format correctly before stuffing it into Excel (your solution a different route in essence) _or_ use an intermediary filter to do the conversion separately before Excel. Anyway, that confirms to me Excel is, in effect, broken when you can't do the above import without resorting to the external tool other than by the manual, change it every time, route. Something to keep in mind if ever do get a call again for "real" work other than the (increasingly rare) call on the plant performance monitors for an update or the like... -- |
#57
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/8/2018 10:51 PM, GS wrote:
... Yeah, I don't have much use for Excel's built-in import feature. In solutions where data gets imported from plain text files, the entire process is managed by VB[A] because the import utility will nearly almost always misinterpret the source data. (Or at very least, cannot be trusted to interpret accurately left to its own!) Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control uses cell types so importing text source data is treated according to its target cell. How that cell displays its data is determined by its preset format. (The fpSpread.ocx does not support ConditionalFormatting and so this has to be done programmatically when cell type formats are not preset. In this control, date cells will optionally include a popup calendar so numeric input is not part of the process entering the date value because it knows which day of which month page you selected.) Likewise, cell formats and/or types can be set programmatically before the data is loaded into a worksheet. This is, IMO, a better way to handle dates than Excel offers and so as I'm able to reproduce here what Excel features are missing, so also can I reproduce in Excel programmatically what it lacks. ... OK, so essentially you throw the baby out with the bath water... :) Yeah, w/o additional tools on the Excel end, I'd write "glue" code to do what I illustrated earlier, read the data with a tool I know I can control to import the given format correctly before stuffing it into Excel (your solution a different route in essence) _or_ use an intermediary filter to do the conversion separately before Excel. Anyway, that confirms to me Excel is, in effect, broken when you can't do the above import without resorting to the external tool other than by the manual, change it every time, route. Something to keep in mind if ever do get a call again for "real" work other than the (increasingly rare) call on the plant performance monitors for an update or the like... Well, my solution is VBA automation of the process, which is not an external tool. In essence, though, I agree that this part of Excel is indeed not well thought out for things beyond general data import. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#58
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/9/2018 10:32 AM, GS wrote:
.... Well, my solution is VBA automation of the process, which is not an external tool. In essence, though, I agree that this part of Excel is indeed not well thought out for things beyond general data import. Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx DAGS for the filename and came up dry, though; who publishes it just out of curiosity? Could, in all likelihood even write wrapper glue code to drive it from Matlab if one wanted to badly enough! :) "Not well thought out" is a polite euphemism for "broke"? :) -- |
#59
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/9/2018 10:32 AM, GS wrote:
... Well, my solution is VBA automation of the process, which is not an external tool. In essence, though, I agree that this part of Excel is indeed not well thought out for things beyond general data import. Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx DAGS for the filename and came up dry, though; who publishes it just out of curiosity? Ok, this is a deprecated ActiveX component I bought after MS 1st introduced the Ribbon UI. It supports read/write of early Excel files, but for all intents and purposes it replaces Excel. I pretty much have complete programmatic control of it. It used to be considered the premiere alternative to Excel back in its day, but now it's been trumped by SpreadsheetGear. There is, though, a newer Spread.net version that is apparently way better than its predecessor... https://www.grapecity.com/en/spreadnet Could, in all likelihood even write wrapper glue code to drive it from Matlab if one wanted to badly enough! :) "Not well thought out" is a polite euphemism for "broke"? :) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#60
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/9/2018 2:03 PM, GS wrote:
.... Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx DAGS for the filename and came up dry, though; who publishes it just out of curiosity? Ok, this is a deprecated ActiveX component I bought after MS 1st introduced the Ribbon UI. It supports read/write of early Excel files, but for all intents and purposes it replaces Excel. I pretty much have complete programmatic control of it. .... AH! "I see more clearly now" what you have said in our other conversations. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for duplicate numbers from ones entered and anoter set | Excel Programming | |||
Dates - Need to display date one month prior to user-entered date | Excel Worksheet Functions | |||
Date subtraction -How to not show negative when 2nd date not entered | New Users to Excel | |||
Check Mark appears when a letter is entered in a field | Excel Programming | |||
Macro to check if data has been entered | Excel Programming |