[AccessD] mailto: in excel export of email address

[Darryl Collins]

Absolutely Rocky.  Many folks originally learn to code starting with Excel's 'macro' recorder.  It is a great and useful bit of kit.  Although as you have noticed, it writes stupidly inefficient code, it is enough to give you something to work with and understand how the syntax should be.

I often use complicated Excel templates that are called from Access VBA.  Once I open up the Excel Template I nearly always pass over running the code to Excel.  This is a simple one liner in Access VBA.

Then Excel runs everything natively and when done passes control back to Access.  I find this is an excellent way of working and has numerous advantages over trying to do everything from Access VBA.

Cheers
Darryl.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Saturday, 5 September 2015 12:02 AM
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Subject: Re: [AccessD] mailto: in excel export of email address

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

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