Hi Garry,
Thank you for your response. The idea is for the financial transactions for the month to be imported into one sheet (this is done via the SQL with defined ranges being used as criteria) and then another sheet would to the calculations (financial report template). On completion, the two worksheets (data & report) would be exported to a new workbook as values only, save, close and then continue through.
What I found was that the defined ranges that I manually created, would get deleted when any existing transaction data was deleted. Thus why I discovered this macro to 'define' the names via VBA. It worked but for some reason, the named ranges excluded 5 rows of transactions.
I've not had much experience working with recordsets and would prefer to do it this way if I can get it work.
I use the formula of... {SUM((A=A)*(B=B)*(C=C)*(D<D)*(AMOUNTS))} in the excel sheets. However, I couldn't figure out how to do this using SQL to report on various headings etc. etc....
This is only a temporary measure until the acquisition and implementation of a new finance system has been done. The system has all this functionality built in and this would become obsolete.
I also agree with everything you've said. There's a lot of room for error doing something like this.
Thank you again.
On Monday, October 7, 2013 11:16:28 PM UTC+8, GS wrote:
Food for Thought...
It appears that the code is creating dynamic ranges, and so the rows
above each header (plus the header) must be *correctly* factored in if
they contain values so the size of the range is accurate.
I'm not clear on why you're even doing it this way when it would be way
easier to load your Access data into a recordset and work with that to
import into your worksheet. A recordset already has fieldnames and
their positions defined and so ADODB can access accordingly via
standard SQL.
Another thing I'm not clear about is why your code *deliberately*
assigns *global scope* to the range names when the fieldnames (headers)
may be identical if/when your file contains more than 1 sheet of data
from the same source (data table). No problem as long as nobody copies
the sheet into a workbook with existing sheets, whereby a name conflict
will occur that (depending on the answer chosen about which version of
the name to use in the target workbook) *may* result in your data (or
existing data) having incorrect values return if there are formulas
that use those names.
On the other hand, if you don't mind using this rather rigid structure
and there's no possibility any flexibility will be needed down the road
then by all means carry on with it. It's not the way I'd go because it
leaves way too much opportunity for disaster to creep in later on, and
it doesn't offer much flexibility in terms of working with the data in
the ways I'm familiar with people wanting to work with their data.
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion