Copying many workbooks into one workbook

[Lost in Quality]

Hi,

I wonder if someone can help me please?

I have many excel files(nearly 500 in total and growing daily) in a folder called "Results Data" on my C drive. The file names are slightly different (serial numbers). Each excel file contains data in Cells A2:E11. I want to be able to copy only the Cells A2, B4, D5 and E10 from each workbook and paste them into a new workbook, each copied workbook on a new row.

I have been trying to change code that I have found on the web but nothing seems to be working, its like monkey tennis with me

Thanks in advance for your time and effort.

 

[Weaver]

Hi Ravi,

Thank you also. Yes there does seem to be more than one way to crack a nut. I am not too sure if its my set up but when I ran your code it cascaded the results, they were not all on the same row.

I had the same problem, hence my rewrite.

LOQ, try the version I submitted, which is a variation on Ravi's method. It's in post #9

 

[Lost in Quality]

Hi Weaver,

It must be one of those days, sorry to keep bothering you.

I can see what you have done between the two programs, just about. When I tried it, the serial number copies for each file but for each cell I get #REF!
Also does it matter if the file extension is .csv. I amended you code from xls.

 

[Weaver]

Did you save the file into your results data folder?

If not, and you don't want to, then change the line

p = ActiveWorkbook.Path & "\"

to p = "c:\results data\"

I don't think them being csv will matter. The line should be

f = Dir(p & "*.csv")

 

[Lost in Quality]

Hi Weaver,

I have done it the way you have suggested and still get the #REF! in each cell
I am being a right dope here, but would it make a difference if the work sheets that are trying to be copied are named after the file name and not the ususal excel default sheet1

Cheers again

 

[Weaver]

I think that might be the problem.

Did my original code work with the csv sheets?

 

[Weaver]

My original code with a slight tweak to look for csv's seems to work

 Sub getData()
    Application.ScreenUpdating = False
    fldr = ActiveWorkbook.Path & "\"
    Set ts = ActiveSheet
    a = Array("file", "A2", "B4", "D5", "E10")
    ts.Range("A1:E1") = a
    d = Dir(fldr & "*.csv")
    r = 2
    Do While d <> ""
        ts.Cells(r, 1) = d
        Set nw = Workbooks.Open(fldr & d)
        For c = 2 To 5
            ts.Cells(r, c) = nw.Sheets(1).Range(a(c - 1)).Value
        Next
        nw.Close
        r = r + 1
        d = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Lost in Quality]

Hi Weaver,

Yes it did. At least I was able to notice that you were looking for a worksheet called Sheet1 in your code. There maybe some hope for me yet.

 

[Weaver]

I think the issue is .csv files don't have a 'sheet' as such before they're opened, for instance you can open them in notepad. Excel actuall 'imports' them and at the same time calls the first tab by the first 31 characters of the filename.

My code doesn't actually look for a sheet called 'sheet1', sheets(1) actually means the first sheet in the current workbook's sheets collection, and since it's a .csv, it can't have more than one anyway.

If your source data was .xls, you'd be better off with Ravi's solution but as it is, mine might be the only one to work in this scenario.

HTH

W

 

[Lost in Quality]

Hi Weaver,

Thanks for your help. The code works great. I have even had a go at using it on some other files, changing the cell names to copy different data.

Have a great week.

Time flies like an arrow, fruit flies like a banana