I have 30 excel workbooks for each year, say year 6,7 and 8, so in total 90
workbooks. Each of the workbooks are stored in this pathname ending in the
year for instance for Year 6 it would be this :-
\\SVR-SBS\FolderName\Folder6

I want to write a Macro to be able to open all the workbooks for one year
where an InputBox asks me which year I need and the answer then causes the
macro to open all 30 workbooks in whatever year I want.

Firstly, is there a way I can list the names of all the workbooks and
secondly if I cant, how do I write code that will only go through the list
of files once, based on the path obtained from the InputBox?

Sub getbooks()

folder = "\\SVR-SBS\FolderName\Folder"
Response = InputBox("Enter Year : ")
If Response < "" Then
folder = folder & Response & "\"
FName = Dir(folder & "*.xls")
Do While FName < ""
Set bk = Workbooks.Open(filename:=folder & FName)
'enter your code here


bk.Close savechanges:=False
FName = Dir()
Loop
Else
MsgBox ("Cannot OPen the year - Exiting Sub")
End If

End Sub


"MurrayB" wrote:

I have 30 excel workbooks for each year, say year 6,7 and 8, so in total 90
workbooks. Each of the workbooks are stored in this pathname ending in the
year for instance for Year 6 it would be this :-
\\SVR-SBS\FolderName\Folder6

I want to write a Macro to be able to open all the workbooks for one year
where an InputBox asks me which year I need and the answer then causes the
macro to open all 30 workbooks in whatever year I want.

Firstly, is there a way I can list the names of all the workbooks and
secondly if I cant, how do I write code that will only go through the list
of files once, based on the path obtained from the InputBox?

Thanks Joel - I will give it a try!!


"Joel" wrote in message
...
Sub getbooks()

folder = "\\SVR-SBS\FolderName\Folder"
Response = InputBox("Enter Year : ")
If Response < "" Then
folder = folder & Response & "\"
FName = Dir(folder & "*.xls")
Do While FName < ""
Set bk = Workbooks.Open(filename:=folder & FName)
'enter your code here


bk.Close savechanges:=False
FName = Dir()
Loop
Else
MsgBox ("Cannot OPen the year - Exiting Sub")
End If

End Sub


"MurrayB" wrote:

I have 30 excel workbooks for each year, say year 6,7 and 8, so in total
90
workbooks. Each of the workbooks are stored in this pathname ending in
the
year for instance for Year 6 it would be this :-
\\SVR-SBS\FolderName\Folder6

I want to write a Macro to be able to open all the workbooks for one year
where an InputBox asks me which year I need and the answer then causes
the
macro to open all 30 workbooks in whatever year I want.

Firstly, is there a way I can list the names of all the workbooks and
secondly if I cant, how do I write code that will only go through the
list
of files once, based on the path obtained from the InputBox?

To open all workbooks in the specified folder number, give this a try...

Sub OpenWorkbooks()
Dim Path As String
Dim Answer As String
Dim FileName As String
Answer = InputBox("Please enter folder number...")
If IsNumeric(Answer) Then
' Note: Do not put trailing backslash on path
Path = "\\SVR-SBS\FolderName\Folder"
FileName = Dir(Path & "\" & Answer & ".xls")
Do While Len(FileName) 0
Application.Workbooks.Open Path & FileName
FileName = Dir
Loop
End If
End Sub

--
Rick (MVP - Excel)


"MurrayB" wrote in message
...
I have 30 excel workbooks for each year, say year 6,7 and 8, so in total 90
workbooks. Each of the workbooks are stored in this pathname ending in the
year for instance for Year 6 it would be this :-
\\SVR-SBS\FolderName\Folder6

I want to write a Macro to be able to open all the workbooks for one year
where an InputBox asks me which year I need and the answer then causes the
macro to open all 30 workbooks in whatever year I want.

Firstly, is there a way I can list the names of all the workbooks and
secondly if I cant, how do I write code that will only go through the list
of files once, based on the path obtained from the InputBox?

Thanks Nick - will give it a shot!


"Rick Rothstein" wrote in message
...
To open all workbooks in the specified folder number, give this a try...

Sub OpenWorkbooks()
Dim Path As String
Dim Answer As String
Dim FileName As String
Answer = InputBox("Please enter folder number...")
If IsNumeric(Answer) Then
' Note: Do not put trailing backslash on path
Path = "\\SVR-SBS\FolderName\Folder"
FileName = Dir(Path & "\" & Answer & ".xls")
Do While Len(FileName) 0
Application.Workbooks.Open Path & FileName
FileName = Dir
Loop
End If
End Sub

--
Rick (MVP - Excel)


"MurrayB" wrote in message
...
I have 30 excel workbooks for each year, say year 6,7 and 8, so in total
90 workbooks. Each of the workbooks are stored in this pathname ending in
the year for instance for Year 6 it would be this :-
\\SVR-SBS\FolderName\Folder6

I want to write a Macro to be able to open all the workbooks for one year
where an InputBox asks me which year I need and the answer then causes
the macro to open all 30 workbooks in whatever year I want.

Firstly, is there a way I can list the names of all the workbooks and
secondly if I cant, how do I write code that will only go through the
list of files once, based on the path obtained from the InputBox?