Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #110656
| From | GS <gs@v.invalid> |
|---|---|
| Newsgroups | microsoft.public.excel.programming |
| Subject | Re: Check how date is entered |
| Date | 2018-05-08 23:51 -0400 |
| Organization | A noiseless patient Spider |
| Message-ID | <pctr8e$b3c$1@dont-email.me> (permalink) |
| References | (27 earlier) <pcsreb$1sv9$1@gioia.aioe.org> <pcss7k$ko7$1@dont-email.me> <pcsvj0$4f6$1@gioia.aioe.org> <pct3co$9ap$1@dont-email.me> <pctlec$1277$1@gioia.aioe.org> |
> 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
Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Check how date is entered jan120253@gmail.com - 2018-05-04 00:31 -0700
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 07:46 -0500
Re: Check how date is entered "Auric__" <not.my.real@email.address> - 2018-05-04 15:55 +0000
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 12:43 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-04 17:37 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 18:49 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-04 21:42 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 23:46 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 04:21 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 07:21 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 09:50 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 09:05 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 10:33 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 10:57 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 20:51 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 11:32 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 21:19 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 07:46 -0500
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 09:37 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-06 20:37 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 19:53 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-06 21:03 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 00:06 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 03:14 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 07:45 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:06 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 13:53 -0500
Re: Check how date is entered malone <malone@nospam.net.nz> - 2018-05-08 08:19 +1200
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 15:35 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 17:38 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 17:26 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 00:19 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 01:31 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 07:45 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 09:45 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 14:34 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 14:09 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 17:06 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 13:48 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 15:02 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 14:59 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 17:04 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 18:53 -0500
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 21:12 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 23:51 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 07:20 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-09 11:32 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 11:35 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-09 15:03 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 15:18 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:34 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 13:44 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 17:40 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 17:50 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 00:02 -0400
Re: Check how date is entered jan120253@gmail.com - 2018-05-07 01:45 -0700
Re: Check how date is entered jan120253@gmail.com - 2018-05-07 01:52 -0700
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 08:16 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:10 -0400
Re: Check how date is entered Ramon-México <cesaramon@gmail.com> - 2018-05-07 22:03 -0700
csiph-web