Code not Working !! Please advise

[DaveUK]

I am trying to open every workbook in a given folder but get error code 1004.

This is baffling me as i kow there are workbooks in this location.

Any advice appreciated.

This is the code i am using (as supplied by Tom many moons ago).

Sub AllWBooks()
Dim sName As String
sName = Dir("C:\Data\*.xls")
Do While sName <> ""
Workbooks.Open Filename:=sName
sName = Dir()
Loop
End Sub


Excel 2000 Pro

[MSP77079]

I put your Sub AllWBooks into a module and watched what happened as I stepped through the routine.

The problem is that sName is does not contain the full path to the file, only the file name.

I changed your sub as follows, and it worked:

Sub AllWBooks()
Dim sName As String

ChDir "C:\Data"
sName = Dir("C:\Data\*.xls")

Do While sName <> ""
Workbooks.Open Filename:=sName
sName = Dir()
Loop
End Sub

[DaveUK]

Many thanks that fixed my problems.

[DaveUK]

Just found another problem !!!


I have used this code as advised:

Sub AllWBooks()
Dim sName As String

ChDir "C:\Data"
sName = Dir("C:\Data\*.xls")

Do While sName <> ""
Workbooks.Open Filename:=sName
sName = Dir()
Loop
End Sub

But, I also need to set up a way to be able to select each workbook that has been opened.
Would an array be the best way or could someone please advise the best way to be able to reference the workbooks by workbook name.

Many thanks

[MSP77079]

See two changes below:

Sub AllWBooks()
Dim sName As String, wb as Workbook '<<< add another dim
sName = Dir("C:\Data\*.xls")
Do While sName <> ""
Workbooks.Open Filename:=sName
set wb = ActiveWorkbook '<<< can now refer to the wb just opened as "wb"
sName = Dir()
Loop
End Sub


You can refer back to the workbook that has the Sub AllWbooks() at any time ... it is "ThisWorkbook".