Hi,

My name is Praveen,
I am working on huge data import and need support on macro.
I have a path "C:\Users\ankopr01\Documents\My Received Files" in which 300 plus excel files will be stored, i need to get the data for few specific column
for which headers are matching as per my compilation(also one header column can have multiple headers like city column can be €œTown€�, €œCentre€� €œCity€�, €œLocation€�)from those 300 files, would be possible for anyone help me writing macro.
This also should copy the files name at the end column. you may also write some error & ignore msg if no matching headers found
Below are the requirements
1. This macro should copy the data only for matching headers
2. There should be one more sheet in which error log should be updated(like if headers matched but no data available it should copy the file name in error log stating no data
a. Error log can be updated for headers matched but no data available
b. No headers matched
c. Neither headers matched neither data available
3. Below the headers table

IntervwrDetails_Name : Interviewer's own name (Please fill below details.) IntervwrDetails_ID : Nielsen Interviewer ID (Please fill below details.) StudyID Project Name Tracking / Non Tracking CapiDeviceID : CapiDeviceID. Please enter your device number: CAPIConsoleName : CAPIConsoleName interview_end CAPILastUpdated : CAPILastUpdated Centre name File Name
City Name
Location Name
Town name


I am fine with editing this macro or you can create new macro
Sub GetDataFromFiles()
Dim strFName As String
Dim strPath As String
Dim strWFile As String
Dim wkbkWF As Workbook
Dim wkShtData As Worksheet
Dim wsWF As Worksheet
Dim rngHeaders As Range
Dim rngFile As Range
Dim rngH As Range
Dim rngF As Range
Dim lngR As Long

Application.DisplayAlerts = False

strFName = ActiveWorkbook.Name
Set wkShtData = ThisWorkbook.Worksheets("All Data") 'Change name
Set rngHeaders = wkShtData.Range("A1:J1") 'Header range
Set rngFile = wkShtData.Range("K:K") 'column of filenames
strPath = "C:\Users\shivamkar01\Documents\My Received Files\"
strWFile = Dir(strPath & "*.xls") ' or .xlsx or .xlsm instead of .xls


Do While strWFile < ""
If strWFile < strFName Then
lngR = wkShtData.UsedRange.Rows.Count + 1
Set wkbkWF = Workbooks.Open(strPath & strWFile)
Set wsWF = wkbkWF.Worksheets(1)
For Each rngH In rngHeaders
Set rngF = wsWF.Cells.Find(rngH.Value)
If Not rngF Is Nothing Then
wsWF.Range(rngF(2), wsWF.Cells(wsWF.Rows.Count, rngF.Column).End(xlUp)).Copy _
wkShtData.Cells(lngR, rngH.Column)
End If
Next rngH
If lngR < wkShtData.UsedRange.Rows.Count + 1 Then
rngFile.Cells(lngR).Resize(wkShtData.UsedRange.Row s.Count - lngR).Value = strWFile
End If
wkbkWF.Save
wkbkWF.Close
End If
strWFile = Dir()
Loop

End Sub

Thanks in advance �
Regards,
Praveen Ankolekar

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

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 ?

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 ?

SQL is not required since (as explained in the sample I linked to) each
Excel file is treated as a database and its sheets as datatables. This
tutorial teaches you how to use ADODB with Excel. If you do this kind
of work on a regular basis then you need to learn the ins-and-outs of
it. (..not meaning to offend you in any way!)

Editing your existing macro would be fruitless waste of time/energy I
don't have either of anymore (I have Lou Gehrig's). Besides, having
someone do that for you doesn't help you 'evolve' your own Excel
skills; -the only way to achieve that is by you doing the work! Better
for you to start from scratch after studying/practising the tutorial...

I might still have a sample project showing how to manage data with
arrays, but this will require opening each file to grab its data. The
ADODB approach is orders of magnitude more efficient and so why I
suggested it. Data field mapping is standard stuff and so there are
plenty of how-to VB[A] examples available online.

Alternatively, if your budget will afford you, there are several VBA
developers that you could 'contract' to develop a solution tailored to
your needs, which can be found online.

--
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

nội dung được up bởi

banh trung thu nhu lan 2016 Bánh trung thu Bibica banh trung thu dong khanh banh trung thu brodard 2016 , chúng tôi chuyên cung cấp các loại bánh Giá bánh trung thu Kinh �ô. �ể biết thêm thông tin chi tiết liên hệ 0975.400.143