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]

This thread concerns a similar issue: https://www.mrexcel.com/forum/excel...e-data-thousands-workbooks-one-worksheet.html

The google drive link in my post there is still active. I think that it should do what you need ... if not, let me know.

 

[Vikas Kumar]

Thanks for reply Trevor. Tried to go ahead with link, but unfortunately I got an error of "400" during run this.

 

[Trevor_S]

Some thoughts on what might cause this...

• Did you try entering a multi-cell range? For example, I notice your example mentions A10:D10. If this is a merged cell, only put the top-left cell (in this case, A10) in my spreadsheet. If it is four distinct cells, give each their own column (and column heading) in my spreadsheet.
• Is there anything else in the selected folder? The macro will try to copy from every file it finds. Therefore there must be no files in the folder (even non-excel files) that aren't to be included on the summary. Even my spreadsheet should be in a different folder from the data that you're trying to collect.

If neither of these are the problem, could you try putting breakpoints in the macro (click on a macro code line and press F9). When you run the macro, it will stop each time it gets to a breakpoint and you need to click the run button to resume. Doing this a few times will allow you to narrow down what part of the macro it is having problems with, and make it easier for me to work out what the cause might be.

 

[Vikas Kumar]

By going through breakpoints met with 'Run time error 1004:' on the following line of,

Set SceWb = Workbooks.Open(myFolderName & mySceFileName) 'Open file found

Clicking again I reach on top And cells are merged from A10:D10, So I am referring this as A10 only.

 

[Trevor_S]

Immediately before the line causing the error, could you try adding this:
MsgBox(myFolderName & mySceFileName)
This should display a message box with the full filepath and name of the file that its going to try to open. Could you see whether it looks like a valid path/filename, and is one that you're expecting it to try get data from?

 

[Vikas Kumar]

Wrote the given line immediate Immediately before the line causing the error and I am able to see valid path of file but post clicking OK it occurs same error again.

May I share the folder of entire files for better understanding.

 

[SpillerBD]

Have you Excel 2016 or Excel 2013 Professional Plus?
I ask because the Get and Transform (previously Power Query) can be used to Get data from multiple files in much the way you are asking.
Far easier to work with than VBA and likely better long term solution.
Mike Girvin's ExcelIsFun YouTube playlist
Also check out PowerPivotPro.com to see where the whole PowerBI can lead as well as Microsoft's PowerBI.com site

 

[Trevor_S]

I'm working on excel 2007, so can't add anything to SpillerBD's suggestion.

Happy to look at the files/folder, upload them into google drive or something similar and post the link here. Remember of course to remove anything sensitive / confidential first.

I know that the code should work in theory, as the original poster of the other thread had messaged me to say so ... so hopefully it shouldn't be too big a problem!

 

[Vikas Kumar]

Using Office 2007 hence unable to give a try with PowerPivot.

Hope uploading folder will create a clear picture therefore attaching the files herewith,

https://we.tl/PiNrVtTa5K

Do let me know if you need further clarification on the same.