Pick up data from files and combine into a new file
Hi,
I have about 10 files of the same appearance. I would like to pick up certain information from each file and then combine that into a new file.
The program should search for a certain heading, in this case for instance "Capital employed". Copy the information under "Capital employed" and paste it into the new file.
The infomation under each heading is separated from a new heading by at least 2 blank lines.
Enclosed is a pick up from 3 files showing the general appearance.
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Re: Pick up data from files and combine into a new file
Hi,
Please, find 3 basic files from which data have been picked up.
The number of basic files is about 10.
The basic files have the same appearance, i.e. 10 rows with data for each heading such as "capital employed" etc.
However, the last basic file might have fewer than 10 rows for each heading.
The parts noted in red are your controls, edit them as needed in your real workbook. The macro is intended to be stored in and run from that destination workbook.
Sub Demo1() Dim Rf As Range With ThisWorkbook: ChDrive .Path: ChDir .Path: End With V = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , Space$(27) & "Select files :", , True) If TypeName(V) <> "Variant()" Then Exit Sub Sheet1.UsedRange.Clear Application.ScreenUpdating = False
For Each W In V With Workbooks.Open(W, 0, True) Set Rf = .Worksheets(1).UsedRange.Columns(1).Find("Capital Employed*", , xlValues, xlWhole) If Not Rf Is Nothing Then With Rf.CurrentRegion .Copy Sheet1.Cells(R& + 1, 1) R = R + 1 + .Rows.Count End With End If .Close End With Next Set Rf = Nothing End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Last edited by Marc L; 07-25-2015 at 09:01 PM.
Reason: optimizing files filter …
Re: Pick up data from files and combine into a new file
Hi,
I have tried to make some Changes of the macro with less success.
The file Destination.xlsm is used for the macro as well as to collect the combined data.
From the original files File1, File2 and File3 are read the values under the first heading "Capital employed" which are combined to Sheet1 under "Destination". The name of Sheet1 is changed to "Capital employed".
In a similar way the values under the next heading, in this case "Capital turnover" , are read and combined to Sheet2 in the destination file. The name of the sheet is changed to "Capital turnover".
The process is repeated until all the headings have gone through. Max 10 headings. A heading stands always over the name "Company" of the original files.
When the process has gone through all the data are collected into new files with corresponding headings.
Re: Pick up data from files and combine into a new file
1) there is only ONE fPATH, you tried to make 3. Put all the files to import into the same folder, list that path under fPATH.
2) The file you uploaded didn't have those sheets, so I had to create them..... do some legwork next time.
So here's the updated macro that has 10 target sheets in the workbook. The sheetname can't include illegal characters like / so we used dashes and then just fix it before the actual search.
Jan, this new code creates worksheets if needed, nothing to set up in Destination workbook :
PHP Code:
Sub Demo2() Const DPATH = "C:\Bibliotek\Dokument\Excel\Macron\" Dim Rg As Range, Ws As Worksheet F$ = Dir(DPATH & "*.xls*"): If F = "" Then Beep: Exit Sub Application.ScreenUpdating = False R& = Sheet1.Rows.Count: EW$ = "¤" Do With Workbooks.Open(DPATH & F, 0, True).Worksheets(1) Set Rg = .Cells(1).End(xlDown).CurrentRegion While Rg.Row < .Rows.Count W$ = Application.Trim(Replace(Split(Split(Rg(1).Value, " (")(0), ",")(0), "/", ChrW(8741))) While Len(W) > 31: P& = InStrRev(W, " "): W = Left(W, IIf(P, P - 1, 31)): Wend
If IsError(Evaluate("ISREF('[" & ThisWorkbook.Name & "]" & W & "'!A1)")) Then For Each Ws In ThisWorkbook.Worksheets If Ws.Name Like "Sheet*" Then Ws.Name = W: Ws.Cells.Clear: Exit For Next If Ws Is Nothing Then With ThisWorkbook.Worksheets: .Add(, .Item(.Count)).Name = W: End With EW = EW & W & "¤" ElseIf InStr(EW, "¤" & W & "¤") = 0 Then ThisWorkbook.Worksheets(W).Cells.Clear: EW = EW & W & "¤" End If
Rg.Copy ThisWorkbook.Worksheets(W).Cells(R, 1).End(xlUp)(3) Set Rg = Rg(Rg.Rows.Count, 1).End(xlDown).CurrentRegion Wend .Parent.Close End With F = Dir Loop Until F = "" SPQ = Split(EW, "¤") For R = 1 To UBound(SPQ) - 1 With ThisWorkbook.Worksheets(SPQ(R)): .Rows("1:2").Delete: .Columns(1).AutoFit: End With Next Set Rg = Nothing: Set Ws = Nothing End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Last edited by Marc L; 07-26-2015 at 10:44 PM.
Reason: adding object variables release …
Re: Pick up data from files and combine into a new file
Hi JBeaucaire,
When I try to run the program I get the message "Subscript out of range" for the row above "if MsgBox etc".
I have changed the basic files File1 to File3 so they should not contain any illegal characters.
Have you any explanation?
The program is now written using 10 fixed Headings, "Capital turnover, "Capital employed" etc. If I would like to have other Headings or variables, still 10 or less, I have to go to the program and change there. Could the program be changed so the headings are taken and read from the basic files? Of course that requires that no illegal characters are used but would create a much more flexible program. The headings can always be found on the row above "Company" in the basic files.
Re: Pick up data from files and combine into a new file
The Destination workbook I provided for you already had the 10 sheet created for you. The one you uploaded does not, so that's why you are getting Subscript errors.
Re: Pick up data from files and combine into a new file
This will be my last comment, I've made my request for you regarding the declaration of variables. The benefits of Options Explicit for keeping errors from creeping into code as you develop it should be self-evident, not needing me to overly underline it again.
So here in the teaching forum, please demonstrate proper declaration of variables that survive the existence of the very important Option Explicit.
Re: Pick up data from files and combine into a new file
Hi JBeaucaire,
As I mentioned a few days ago I get the message "Subscript out of range" for the row above "if MsgBox etc".
I have changed the basic files File1 to File3 so they should not contain any illegal characters. The destination file where also you will find the macro has 10 sheets. Do you have any explanation
why I cannot run the macro?
The program is now written using 10 fixed Headings, "Capital turnover, "Capital employed" etc. If I would like to have other Headings or variables, still 10 or less, I have to go to the program and change there. Could the program be changed so the headings are taken and read from the basic files? Of course that requires that no illegal characters are used but would create a much more flexible program. The headings can always be found on the row above "Company" in the basic files.
Re: Pick up data from files and combine into a new file
Hi Marc L,
I have tried your macro Demo2 enclosed.
However, even if it seems to go through, I do not get any data from the basic files File1, File2 and File3 in the directory C:\Bibliotek\Dokument\Excel\Macron\Files\
Re: Pick up data from files and combine into a new file
Hi,
I have tried to use a macro "demo2" in file "Destination"
developed by Marc L but got the the message for variable F$ on the 3 rd line "Variable not defined". I thought it was a string. But it must obviously be something else?
Re: Pick up data from files and combine into a new file
Hi JBeaucaire,
I have downloaded your macro, Demo2, but I cannot get it to function. From your No 21 contribution I can see that the final result is excellent with all 10 sheets. The question is why it is not functioning with me?
I have copied your file, added the basic files with data, File1, File2 and File3. All files can be found in the folder Macron with the path "C:\Bibliotek\Dokument\Excel\Macron\".
First time I run it I got the message "variable not defined" for "P&" and "SPQ". I added them to "Dim". Then the program at least accepted the changes. But it did not start running.
Re: Pick up data from files and combine into a new file
I opened the file from your post #24 and ran it changing only the path to my files, and it runs fine. I see nothing that should stop if from working for you.
Re: Pick up data from files and combine into a new file
Hi,
The program now functions as desired.
However, a small change might be suitable.
Now , the headings consisting of an empty row plus the item in question plus the row "Company plus years" are repeated on the work sheet. It would be better to have it only once at the top and then delete all the others to form a big table with only the heading once. Such a big table with data and only one heading would be easier to work further with.
Re: Pick up data from files and combine into a new file
Originally Posted by JBeaucaire
Marc's original code used undeclared variables, which they believe is a fine practice. We will have to agree to disagree
My variables are declared ! For example, F$ = Dir(DPATH & "*.xls*")
is the same as in VB Dim F as String = Dir(DPATH & "*.xls*") !
It's just a BASIC heritage …
And very easy to check : using a break point at the beginning of code or
in step by step mode (hitting F8 key), just see the Local variables window !
I hope this time my point of view won't be edited like in my previous post …
Re: Pick up data from files and combine into a new file
Marc, you're point is fine, but limited. The benefits of OPTION EXPLICIT are much more than just forcing you to declare your variables in a DIM. It also points to all detectable errors in the code as you go. So having to declare your variables at the top is a small price to pay for that "spell-checking" capability being there helping all the time.
So, yes, you're method of declaring as you go is fine in theory (and in practice for experienced programmers), but that method does not play nice with OPTION EXPLICIT which (IMO) offers much more important help to learning coders.
Re: Pick up data from files and combine into a new file
Hi,
I have got help to pick up data and combine them into new worksheets. It works fine.
In some cases you might be interested in combining the data on each worksheet into one big table, leaving out 3 rows (blanc, the studied variable and Company + years) except for the rows at the top. I have got a suggestion of a macro but it does not function so well for me.
If one instead starts with the worksheets created, which work properly, and limit the macro to only delete the rows except the 3 heading rows at the top how would the VBA code then be?
LinkBack URL
About LinkBacks
Pick up data from files and combine into a new file
Register To Reply
icon below to give reputation feedback, it is appreciated.
Hi ! Try this demonstration !
Try this new demonstration !
Re: Pick up data from files and combine into a new file
Originally Posted by JBeaucaire
Bookmarks