[AccessD] mailto: in excel export of email address

[Rocky Smolin]

I always use TransferSpreadsheet as a first line solution.  But, as you
point out, it doesn't give you much control.  So I've done a lot of direct
writing of data to Excel especially where formatting or formulas are
involved or anything other than a direct transfer of a data value from an
Access field to an Excel cell.

The object model for Excel had a bit of a learning curve, but once you get
it, virtually everything you can do in Excel you can do to a spreadsheet
from a bit of code in Access.

The real breakthrough came when I read somewhere about recording a macro in
Excel and then cribbing out the code it generates and pasting it into your
Access CBF. Same tick as using the QBE grid in Access to generate complex
SQL statements for you.  Once I got the hang of that, I didn't have to
research how to automate some Excel feature - just recorded the macro and
walla! The code I needed.

I'm going to put an apostrophe in front of that email address and see what
happens at the client site.  Stay tuned for an update...

Thanks

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Friday, September 04, 2015 1:23 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] mailto: in excel export of email address

I know you wont take my advice, but really, automation of Excel combined
with copyfromrecordset or ead/writing arrays to ranges is very powerful and
flexible and takes your level of control to a whole new level. Many times
output needs formatting, breaking up into tabs, etc.

Another HUGE benefit I got from this approach was that Access 2013 was
hogging memory and not releasing it (ie, impact accumulative throughout
session, until shut down) whilst using DoCmd.OutputReport. I found that
sending a massive amount of data to Excel via automation, and continuing to
process the data through the automation brought only a few mb of RAM use
accretion, whereas outputting smaller queries in a loop outputting a batch
of reports blew up memory really fast.

I would be surprised if TransferSpreadsheet were quite as naughty as
OutputReport... but it hypothetically could be so.

For a die hard Excel developer who turns to Access just to retrieve data, my
first choice is to use Access as a backend, my second choice is to let
Access automate Excel, and my third choice would be to let Access pump data
to Excel in an unmonitored and unmanaged way, like TransferSpreadsheet,
which I would not be at all surprised is loaded with gotchas.

YMMV.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com