Trying to fetch certain details from multiple files

[Vikas Kumar]

Hi Friend,

I am trying to gather particular details from a bunch of workbooks located in a folder. Suppose I have a folder "PI 17 - 18" on my desktop which contains around 370 Performa Invoices. Now I want to collect only few details from each perfoma Invoice in below Format,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Agency Name[/TD]
[TD]Adveriser[/TD]
[TD]Start Date[/TD]
[TD]Net Amount[/TD]
[/TR]
[TR]
[TD]A10:D10[/TD]
[TD]A18:B18[/TD]
[TD]E12[/TD]
[TD]G46[/TD]
[/TR]
</tbody>[/TABLE]

Above mentioned ranges capture corresponding details. I have tried to do the same with following Macro but unfortunately unable to get through,

---

Sub Example()
Dim Mypath As String
Dim wbk As Workbook
Dim Targetfile As Worksheet
'Where all files are located
Mypath = "C:\Users\VIKAS KUMAR\Desktop\PI 17 - 18"
'In this workbook I want to compile details (Unsaved)
Set Targetfile = ThisWorkbook.Sheets("Sheet1")
Do While Mypath <> ""
Set wbk = Mypath.Workbooks.Worksheets("Sheet1")
Range("A10:D10").Copy
Targetfile.Activate Range("A2").PasteSpecial
Range("A18:B18").Copy
Targetfile.Activate
ActiveCell.Offset(0, 1).PasteSpecial
Range("E12").Copy
Targetfile.Activate
ActiveCell.Offset(0, 1).PasteSpecial
Range("G46").Copy
Targetfile.Activate
ActiveCell.Offset(0, 1).PasteSpecial
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

---

Also for ease attaching herewith a sample PI.

https://wetransfer.com/downloads/45...b650139259208470336fc0ff20170521112532/497196


Thanks for Help in advance.

 

[Trevor_S]

Thanks, but I'm having difficulty downloading this because of its size! I'm assuming that there are a lot of files there ... could you just do a couple please - if I can get it to work for them, it should work for all.

 

[SpillerBD]

I found the following thread and modified the macro. Tested on the first 20 files OP linked.
https://www.mrexcel.com/forum/excel...same-cell-range-file-name-multiple-books.html

In testing against those files, it is revealed there re inconsistencies with the value in the date cell not being a proper date.
Also, the subtotal does not always exist in G46

Public Sub GetData()
Dim MyPath As String
Dim MyRow As Long, MyPasth As String, MyCol As Long
Dim fso As Object, fldStart As Object, fl As Object
Dim ts As Worksheet
' Initialization
    MyPath = "C:\Users\Brian\Downloads\PI 17 - 18\PI 17 - 18\"
     
    MyRow = 1
    Set fso = CreateObject("scripting.FileSystemObject")
    Set fldStart = fso.GetFolder(MyPath)
    Cells.ClearContents
    Set ts = ThisWorkbook.Sheets("Sheet1")
    
    Application.ScreenUpdating = False
    
' 1st section
' Find all the matching files in this directory.  Open the file, get the data
    For Each fl In fldStart.Files
        If fl.Name Like "*.xl*" Then
            Workbooks.Open Filename:=MyPath & "" & fl.Name
            MyRow = MyRow + 1
            ts.Cells(MyRow, 1) = fl.Name
            ts.Cells(MyRow, 2).Value = ActiveSheet.Range("A10").Value
            ts.Cells(MyRow, 3).Value = ActiveSheet.Range("A18").Value
            ts.Cells(MyRow, 4).Value = ActiveSheet.Range("E12").Value
            ts.Cells(MyRow, 5).Value = ActiveSheet.Range("G46").Value
            
            
            
            
            ActiveWorkbook.Close savechanges:=False
        End If
    Next fl
    
' finalization
    Application.ScreenUpdating = True
    Set fso = Nothing
    Set fldStart = Nothing
End Sub

 

[Vikas Kumar]

Thanks for point out these things and Apologies to not explain earlier about whole data structure! I was also missed this.
For dates, I think we may recognise / update dates later once it fetched. And Since subtotal value is exist in G46 and somewhere it kept in G48 then can we determine a such condition to pick among of anyone cell where it does not found in G46.



Trevor_S: I have uploaded couple of sample over here. If you are still unable to download this then request to go with my first link and give a try by just changing values and file name As all files follow same format.

https://drive.google.com/open?id=0Bxtc6jUuxe_pRWhtRUFZdXB6SzA


Please note that Amount value which stored either in G46 or G48 in few files.

 

[SpillerBD]

For G46\G48 the compare logic needs to be valid.

We could replace

ts.Cells(MyRow, 5).Value = ActiveSheet.Range("G46").Value

with

ts.Cells(MyRow, 5).Value = WorksheetFunction.Max(Val(Range("G46").Value), Val(Range("G48").Value))

I must admit that is weak on the validation but may be suitable in this case.

 

[Trevor_S]

I tried my original macro on your three files and it worked!
This is my original spreadsheet, adapted to show your fields: https://drive.google.com/file/d/0B_dlXxlE0XVBMWExU2h3bmxCajA/view?usp=drivesdk

I've used both of the amount fields, rather than try to determine the correct one. I then created a new sub folder and put your three files in it. Clicking the button let me pick the new sub folder. Then in the save screen that followed, I gave the results file a new name in the same folder as my spreadsheet - not in the new sub folder.

This is the results file that it generated:
https://drive.google.com/file/d/0B_dlXxlE0XVBZDdOZXlGUDhTYjA/view?usp=drivesdk

Hope that helps!

 

[Vikas Kumar]

Eureka guys!!!

I think my search has been finished over here. Both the last solutions worked perfectly.

Appreciate your continuous support and prompt response.