Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #51   Report Post  
Old May 8th 18, 10:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 952
Default 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   Report Post  
Old May 8th 18, 10:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 952
Default 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   Report Post  
Old May 9th 18, 12:53 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 63
Default 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   Report Post  
Old May 9th 18, 03:12 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 63
Default 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   Report Post  
Old May 9th 18, 04:51 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 952
Default 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   Report Post  
Old May 9th 18, 01:20 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 63
Default 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   Report Post  
Old May 9th 18, 04:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 952
Default 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   Report Post  
Old May 9th 18, 05:35 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 63
Default 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   Report Post  
Old May 9th 18, 08:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 952
Default 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   Report Post  
Old May 9th 18, 09:18 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 63
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for duplicate numbers from ones entered and anoter set Goldie Excel Programming 1 May 29th 09 08:47 AM
Dates - Need to display date one month prior to user-entered date brettopp Excel Worksheet Functions 13 December 3rd 07 05:58 PM
Date subtraction -How to not show negative when 2nd date not entered Edward[_2_] New Users to Excel 2 September 27th 07 03:03 PM
Check Mark appears when a letter is entered in a field Gina Excel Programming 2 February 22nd 07 08:23 PM
Macro to check if data has been entered Mike R. Excel Programming 1 December 20th 04 11:14 PM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017