[AccessD] mailto: in excel export of email address
Darryl Collins
darryl at whittleconsulting.com.au
Fri Sep 4 01:11:36 CDT 2015
Yes... Excel and dates are a whole chapter of SNAFUness in itself. There are just so many gotcha's if you are not careful. Keep in mind that Excel will convert the 'It looks like a date' value to an actual value. All dates in excel are values with a pretty dress on top as per the formatting rules.
Common issues include, but not limited to;
Part numbers with a / or - getting auto converted into dates. 2/3 for example or 3-2014 etc.
Any date passed via VBA is treated as (and convert to) a US date format (mm/dd/yy) *regardless* of the regional setting.
The whole 1900 vs 1904 date issue (always problematic when dealing with Office for Mac users for starters)
Imported data with dates from one format (say US mm/dd/yyyy getting converted to dd/mm/yy or visa versa)
And the list goes on.
This is part of the reason a lot of sensible folks say that all values should be stored as strings when passing them thru Excel and you explicitly handle them when you need to.
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, 4 September 2015 4:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] mailto: in excel export of email address
Not to mention anything that looks like a date.
I once had a project which received plant analyses with loads of values such as "21 Sep".
Excel always wanted to convert them to numbers and display them as the 21st of September in the current year.
Grrrr,,,,
On 4 Sep 2015 at 5:25, Darryl Collins wrote:
> Yes... The apostrophe text control is a legacy going back to Lotus 123
> days.
>
> The original Lotus label prefixes to control text alignment were:
>
> ' APOSTROPHE Left-aligned
> ^ CARET Centered
> " DOUBLE QUOTE Right-aligned
> \ BACKLASH Repeating
>
> So if you wanted something to be centred you would enter ^MyValueHere.
> This is back in the days before toolbars and it was all text based
> controls.
>
> Over the years only the ' survived in Excel and it is used to force
> the Excel to treat the cell value as a string (or Text in Excel
> parlance) rather than aligning (although being a string is usually
> default left aligns regardless. It is important to be able to do this
> as Excel will convert anything that looks like a number into a value
> if it can, and that process is irreversible. This is a huge problem
> for things like
>
> - Anything with a leading zero
> - Anything that looks like a scientific number (Hotel / meeting rooms
> and part numbers are particularly problematic here) - Any number >
> than 15 characters, so all credit card values for example.
>
> For example, if your room or part number is 12E3 then Excel will
> convert that to 12,000 for you. Bloody useless if you really did mean
> "12E3"
>
> Or lots of part numbers have leading zero which are material to the
> result. So you really need 000013120 to read that, not 13120.
>
> Anyway, Hopefully that will shine some light onto the history and
> necessity of the apostrophe ' in Excel.
>
> Cheers
> Darryl
>
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Rocky Smolin Sent: Friday, 4 September 2015 10:47 AM To: 'Access
> Developers discussion and problem solving' Subject: Re: [AccessD]
> mailto: in excel export of email address
>
> I forwarded your post to my client and told HER to try it. I'll let
> you know what she says.
>
> Tks
>
> R
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Stuart McLachlan Sent: Thursday, September 03, 2015 2:51 PM To:
> Access Developers discussion and problem solving Subject: Re:
> [AccessD] mailto: in excel export of email address
>
> Give it a try. Excel does all sorts of weird things, this is one of
> them. A leading apostrophe is treating as a "text indicator". Try
> entering '42 for example into a cell
>
> Although you will see the leading apostrophe in the formula bar, you
> won't see it in the actual spreadsheet,
>
> If you select the cells and copy and paste them into some other
> application, the apostrophe won't be carried across.
>
> If you "paste values" in the spreadsheet, it won't carry the
> apostrophe across (although a standard paste will)
>
>
>
> On 3 Sep 2015 at 7:27, Rocky Smolin wrote:
>
> > Stuart:
> >
> > Won't that have the effect of the email address in the spreadsheet
> > having an apostrophe in the first position?
> >
> > Rocky
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On
> > Behalf Of Stuart McLachlan Sent: Wednesday, September 02, 2015 3:23
> > PM To: Access Developers discussion and problem solving Subject: Re:
> > [AccessD] mailto: in excel export of email address
> >
> > It's a long term Excel "feature".
> >
> > https://support.microsoft.com/en-us/kb/291209
> >
> > You probably have the Autoformat optione turned off in Exccel while
> > the cilent has it turned on.
> >
> > Suggest you try using the option of prepending an apostrophe to
> > that field inr your export query.
> >
> >
> > On 1 Sep 2015 at 14:14, Rocky Smolin wrote:
> >
> > > Dear List:
> > >
> > > I'm using TransferSpreadsheet on a table that has an email address
> > > in a text field. when I export no problem, but when the client
> > > exports the email address shows up in the spreadsheet with
> > > 'mailto:' prefixed to each email address. I'm using 2003. She is
> > > using 2010 or perhaps 2013 (hard to tell from the screen shot).
> > >
> > > Is this something that 2010 is adding? Is there a solution?
> > >
> > > MTIA,
> > >
> > > Rocky Smolin
> > > Beach Access Software
> > > 858-259-4334
> > > www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com
> > > <http://www.e-z-mrp.com/>
> > > Skype: rocky.smolin
> > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
More information about the AccessD
mailing list