VBA to Open all files from a folder and copy data

[ravi4ever]

Hi all..

I have 21 files in a folder and I want excel to extract data from each file and collate it to the Macro file (Tracker.xls) and update it in a different format..

All files have a Sheet "Template" and the macro should pick the data from A5:J5 and each row below till a blank row, the copied data should go to the "Temp" sheet of the Macro..

Validation - If A5 is blank then skip this file and leave a msg box..

now the data collected in sheet "Temp" should go to Sheet "New_Tracker" of the macro file in the following manner..

Source example
<TABLE style="WIDTH: 394pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=523 border=0 x:str><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=46 height=17>Month</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>Period</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>Amount</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Desk Code</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>Description</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=47>Action</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>Owner</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=52>Ageing</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>Reference</TD></TR></TBODY></TABLE>

Destination example
<TABLE style="WIDTH: 513pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=681 border=0 x:str><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17>Co. Code</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=46>Month</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>Period</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>Amount</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=119>Transaction Date</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=46>Status</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Desk Code</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>Description</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=47>Action</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>Owner</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=52>Ageing</TD></TR></TBODY></TABLE>

The file names have first two digits as company numbers which I want to be inserted in the the first column shifting others on right in sheet "Temp" of the Macro file (Tracker.xls)..

From "Temp" sheet copy A1:D1 and xlDown, to sheet "New_Tracker" from last row of column "A" i.e. if there is any data on Axx and it should start from Axx+1..

Column E is same as C

From "Temp" copy values from E1:J1 and xlDown, to sheet "New_Tracker" from last row of column "G" i.e. if there is any data on Gxx and it should start from Gxx+1..

Column K will have the formula =ROUND(($B$1-C3),0) / =ROUND(($B$1-C"same row"),0)

And do all this in a loop with all files..


Currently I have created a recorded macro and some modifications to it do the same..

Dim a, b As String
  'CO 05
 
    Sheets("Temp").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
'have changed the file names to 2 digits only, but originally its like 05 AAAA_000_00000
    Workbooks.Open Filename:= _
        "Z:\Folder\macro\[COLOR=red]05[/COLOR].xls"
    Sheets("Template").Select
a = Range("A5").Value
b = Range("A6").Value
If a = "" Or _
b = "" Then
MsgBox "Cell A5 or A6 is blank, hence will skip this file.. Please note and update it mannually"
Else
    Range("A4").Select
    Selection.AutoFilter
    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Tracker.xls").Activate
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "[COLOR=red]05[/COLOR]"
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1:D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Tracker").Select
    Range("A65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("Temp").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Tracker").Select
    Range("E65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("Temp").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Tracker").Select
    Range("G65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A11").Select
 
End If
    Windows("[COLOR=red]05[/COLOR].xls").Activate
    ActiveWindow.Close
    Range("A1").Select
 
'for column E and K
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "Open"
    Range("F3").Select
    Selection.Copy
    Range("E3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("K:K").Select
    Selection.ClearContents
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "Ageing"
    Range("K3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=ROUND((R1C2-RC[-8]),0)"
'the date is available on B2 and then the column heading and then from A3 the data follows
    Range("K3").Select
    Selection.Copy
    Range("I3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 2).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A3").Select

Currently I am using the same code for each 21 files by changing the red values which is a long 1600 lines code, so probably their's a way by looping..

May be someone don't want to use the "Temp" sheet and come up with a programme to meet the requirement however having a "Temp" sheet was my level of thinking to achieve the target..

have found similar thing but have no idea to change it according to my criterias.. here

I have tried to provide all the details in one go, its very long right?.. please ask for more if needed ..

 

[Andrew Poulsom]

See if you can adapt one of the examples here:

http://www.rondebruin.nl/copy3.htm

It may be easiest to get all the data then do your manipulations.

 

[ronahaim]

hi,
i will be happy to know if you found an answer to this question, because i'm in the same issue now.
thanks!

 

[Apocolyse]

U just need to list the name of each file into a range - lets say A1:A1600, then make a for loop like this:
Dim wb as Workbooks
for i = 1 to 1600
wb = Workbooks.Open("Z:\Folder\macro\" & range("A" & i).value & ".xls") (to open each files, then do what you need for each file)
.......................
wb.Close False (to close the workbook you open and not saving any changes)
Next i