[AccessD] Fwd: Re: Is it better to call Excel Macro from access, or manipulate from Access?

[Darryl Collins]

"...a simple CopyFromRecordset would avoid the need for creating and deleting header rows altogether."

Sure, but this assumes that once you get the data to Excel you are not going to do much (or indeed, anything) else with it.  And that might be fine and exactly the case.   If you are just dumping data into cell A1 and leaving it, then copy from RS will work great and why bother with Excel VBA at all.  But often getting the data into Excel is just the first step of a multi-step process. 

In my example where I prefer using the Excel template code, Excel does a lot of work with the downloaded data - and letting Excel do that work natively is a lot easier and faster to work with that trying to put it all into Access VBA.

As I originally said, the real answer is "it depends".  Those dependencies include your own personal coding style.  I like having the Excel code separate as it is much easier to work with when debugging - but this is likely due to the Excel template in my example doing some complicated and tricky stuff - ergo it is easier to develop and test it 'stand-alone' - or at least I find it so.

I can see pros and cons both ways and would use the best method for the task at hand.

It is easy enough to establish an ADO connection between Excel and Access (or SQL Server) so Excel can control them back as well.  This can be useful if you need to return data based on a result determined by Excel based on the original downloaded dataset.

Good luck. If you need help with the Excel side feel free to email me offlist or try the Excel-L list.  Looks like you could use a hand with that based on your example code provided.  The VBA code recorder does an ok job and will give you the right idea, but the code it writes is barely functional and largely flaky and bothersome for regular use.

As a rule of thumb you can usually clean it up by about 80%.

Cheers
Darryl

 







-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, 15 May 2015 6:26 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Fwd: Re: Is it better to call Excel Macro from access, or manipulate from Access?

Some opinions from additional excel experts David. I also by just gut feeling would have coded this from Access.

On the flip side I might have treated Access like a backend and written similar queries and transformations you might be running in Access with ADO and put the project in Excel, if possible.
---------- Forwarded message ----------
From: "John Nurick" <john.nurick at nurick.net>
Date: May 15, 2015 4:22 AM
Subject: Re: [AccessD] Is it better to call Excel Macro from access, or manipulate from Access?
To: <EXCEL-L at peach.ease.lsoft.com>
Cc:

+1. Also, to my tidy mind it always feels neater to have all the code in 
+the
same project (not counting add-ins).

-----Original Message-----
From: Microsoft Excel Developers List [mailto:EXCEL-L at PEACH.EASE.LSOFT.COM]
On Behalf Of Rory Archibald
Sent: 15 May 2015 09:18
To: EXCEL-L at PEACH.EASE.LSOFT.COM
Subject: Re: [AccessD] Is it better to call Excel Macro from access, or manipulate from Access?

Unless there is a need for the macros to exist in Excel (e.g. they may need to be run again separately to the original Access processing) I see no benefit in having them in Excel. I'd do everything in Access. The process itself sounds unnecessarily complicated to me - a simple CopyFromRecordset would avoid the need for creating and deleting header rows altogether.

--------------------------------------------------------------------------
The EXCEL-L list is hosted on L-Soft international's LISTSERV(R) software running on Microsoft Windows Server 2008 R2. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/excel-l.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-L
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com