On Friday, August 5, 2016 at 12:37:43 AM UTC+5:30, GS wrote:
Perhaps a different approach would be a better way to go! You can use
ADODB to read/write closed workbooks, and work with data fieldnames you
define. So if your data is structured (all cols hold same data type in
same order) you can access any field using a pre-defined set of header
(field) names stored in an Enum. This permits your code to always ref
the correct field (column) regardless of what label it has.

The same can be done using an array to hold the data. The purpose for
the enum is to ref the column position for the desired data...

Sample fields (headers):
FirstName | Lastname | Initials | Address1 | Address2 | Prov/State |
ZipCode | M/F | Phone1 | Phone2 | Email

..where loading these and their underlying data into a recordset (ADO)
or array (VBA) is a 1-step process, but the latter requires the source
file is open in Excel.

In the case where your fields don't align or have same names, the usual
approach is to 'Map' fieldnames so you can cross-ref to the correct
data.

Might seem like a lot of work but once done it results a reusable
utility. Given the volume of files and any amount of frequency
processing them, it would certain maximize your productivity.

You can download an Excel ADODB tutorial and working examples he

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Hi Garry,

is it possible for you to edit this macro as i do not have SQL installed or knowledge on the same ? or create please provide an new macro in seperate file ?