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