0

Similar to this post

Iterate through spreadsheets in a folder and collect a value from each

I am trying to do the same but rather than a single value, I would like to loop through up to 52 different arrays, e.g (using pseduo code) the path being external 

For each workbook in path
 While week < 52
   week1 = path.workbook.sheet1.range(A1:J20);
   consol.range("A" & row & ":J " & row2) = week1
   row = row + 20
   row2 = row2 + 20
   week++
 Wend
Next

Is this even possible?

Community
  • 1
  • 1
Mike
  • 58
  • 2
  • 10
  • If you're going the copy-paste route, the workbook must be opened first. – WGS Dec 19 '13 at 11:09
  • Is there a way to do it without? I'm thinking, since the range is always fixed (A1:J20) that I can create "holder" linking to that book on a tab, and then for each workbook update cell "C1" on that holder tab which updates a placeholder range in the same book? – Mike Dec 19 '13 at 11:19
  • 1
    See if [this](http://stackoverflow.com/questions/15978391/how-can-i-read-information-from-a-specific-cell-in-a-closed-workbook-then-paste) works for you. – WGS Dec 19 '13 at 11:23
  • yes it's possible to grab all these values from closed workbooks using a loop of [ExecuteMacro4Excel](http://vba4all.wordpress.com/category/vba-macros/various-ways-to-pull-data-from-another-workbook-closedopened/) –  Dec 19 '13 at 11:28
  • You can use ADO, deliberate links or XLM for closed books. But for 200 cells in the range of interest, you might finding opening the files directly to be the superior approach rather than sticking to working with closed books – brettdj Dec 19 '13 at 11:37
  • I wouldn't copy and paste it, or open it. I would link it with a formula, manually or with vba, where ever you want to paste it, then if you didn't want the formula, copy and paste values after. – MakeCents Dec 19 '13 at 13:42

1 Answers1

0

I just checked my notes, and i got this:

week1="'" & path & "[" & FileName & "]" & Sheet1.Name & "'!" & _
Range("A1:J20").Range("A1").Address ( ,,xlR1C1)

I think it still needs some tweaking, because i was lost at the "A1" part of the code...

Does this help you ?

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24