Open files that are listed in column A

[Shazam]

Hi everyone:hi:


I have this code below that will combined files into one workbook.


Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.ScreenUpdating = False
Path = "C:\Test" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)

For Each WS In Wkb.Worksheets
If InStr(1, WS.Name, "Incentive") <> 0 Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next
Wkb.Close False
FileName = Dir()
Loop
Sheets("Sheet1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub



But I would like to open the files that are listed in column A. How can the code below be combined with the code above.


Dim R As Range
For Each R In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open ("C:\Test" & R.Value)
Next R

[vonpookie]

Untested, but I believe this should work:

Sub CombineFiles()
Dim Path As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim R As Range

Application.ScreenUpdating = False
Path = "C:\Test" 'Change as needed

For Each R In Range("A1", Range("A65535").End(xlUp))
Set Wkb = Workbooks.Open(FileName:=Path & "\" & R.Value)

For Each WS In Wkb.Worksheets
If InStr(1, WS.Name, "Incentive") <> 0 Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next
Wkb.Close False
FileName = Dir()
Next R

Sheets("Sheet1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[Shazam]

Thank You for replying vonpookie.


I'm getting a runtime error '1004' this line of code.


Set Wkb = Workbooks.Open(FileName:=Path & "\" & R.Value)

[Shazam]

Ok my fault It was the file path I had to change. But it will only open the first workbook not the rest. It gives me this line error.


FileName = Dir()


Run-time error '5':
Invalid Procedure call or argument

[mdmackillop]

Hi Shazan,
I'm not clear if you're wanting to combine all the files listed in A. If you just want to open them, your code is OK except a missing "\" and possible extension

Dim R As Range
For Each R In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open ("C:\Test\" & R.Value & ".xls" )
Next R

[Shazam]

Ok my fault It was the file path I had to change. But it will only open the first workbook not the rest. It gives me this line error.


FileName = Dir()


Run-time error '5':
Invalid Procedure call or argument


I figuered it out I had to changed it to:


FileName = r()



Thank You so much vonpookie!

[Shazam]

Hi Shazan,
I'm not clear if you're wanting to combine all the files listed in A. If you just want to open them, your code is OK except a missing "\" and possible extension

Dim R As Range
For Each R In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open ("C:\Test\" & R.Value & ".xls" )
Next R



Hi mdmackillop thank you for replying. Yes I do want to combined the files. I think your right I'll test your code and post back.

[malik641]

Hey Shazam, don't you think you should do a preliminary check if the file exists so you don't get the error, even if the file address is invalid? And if it is invalid maybe you could highlight the cell(s) with the bad filename.