Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Get data for matching headers thru macro

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Get data for matching headers thru macro

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Get data for matching headers thru macro

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 ?
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Get data for matching headers thru macro

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

  #5   Report Post  
Junior Member
 
Posts: 2
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Macro on moving data with headers into larger file.. Nick_GB Excel Discussion (Misc queries) 0 December 20th 12 11:59 AM
Import matching up the Headers name instead of the Column letter Crazyhorse Excel Programming 1 June 26th 09 05:41 AM
$%Macro to Copy from one workbook to other when matching data Tiger Excel Discussion (Misc queries) 0 July 30th 07 08:42 AM
Matching data macro in VBA Werner Excel Programming 0 June 14th 05 08:15 PM
Matching headers and inserting values from a table jacqui[_3_] Excel Programming 0 January 12th 04 04:00 PM


All times are GMT +1. The time now is 09:32 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"