-1

I have about 200ish workbooks That I need opened in a dir containing thousands of files. The name of these 200 files have been placed in Sheet 4 B3:B231.

Whenever I run the macro however, I only get the code to work on 4 of these files, 1 of the 4 isn't even mentioned in the specified range. 

Dim wb As Workbook, shtDest As Worksheet
Dim MyFile As String
Dim Filepath As String
Dim WoBo As Variant
Dim DirArray As Variant
DirArray = Sheets("Sheet4").Range("B3:B231").Value

Filepath = "C:\"
MyFile = Dir(Filepath)

Set shtDest = ThisWorkbook.Sheets("Sheet1")

For Each WoBo In DirArray
      'code  
Next WoBo
End Sub

I'm quite new to arrays and I don't really understand the For Each line, so I suspect the error to be there.

G. Koen
  • 33
  • 6

1 Answers1

0

Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.

Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)

For i = LBound(dirArray) To Ubound(dirArray)
    If instr(dirArray(i),"xls") > 0 Then '<== very basic check
        Workbooks.Open(dirArray(i))  '<== should be full filepath of file
        ' Do stuff
    End If
Next
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?" – G. Koen Nov 14 '18 at 10:25
  • The cells you are reading in from should contain the full file path to the file I believe C:\Users\path.....\workbookX.xlsx. – QHarr Nov 14 '18 at 10:34
  • Did you try that? – QHarr Nov 14 '18 at 11:39
  • I'm not sure I understand. The files that have to be read are all in the same directory, C:\Users\path...\workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. . – G. Koen Nov 15 '18 at 05:52
  • You need concatenate the path on the name before passing to workbooks.open – QHarr Nov 15 '18 at 06:23
  • I've slightly altered the code, based on yours. And got it to work. Thank you. – G. Koen Nov 15 '18 at 12:50