Excel selecting multiple workbooks to search at once

[Nickooo87]

Hello,

Currently im trying to build a system that will allow to see what
rooms have been booked on what days to show when a room is free to be
booked. I am currently querying data from an access database and also
matching up data in an excel spreadsheet. In the access database is
all the info about the booking e.g day time room code etc and in the
excel spreadsheet there is the information about what equipment the
room has e.g white board, projector etc.

What I am am trying to do is build an interface inside excel to query
both the access and excel datasets. A user will be able to enter a
room code and when they click enter the data will be pulled from both
access and excel and onto a spreadsheet. However in excel the data is
contained in multiple worksheets defined by the different building
blocks. This is where I have become stuck as i need to be able to
query all the worksheets at once for the room code entered. Here is my
code

With cn1
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Excel.ActiveWorkbook.Path
& "\Roomdata.mdb;Persist Security Info=False"
.Open

End With





'===============================================================
'After connecting, the recordset is then populated with the required
data
'===============================================================

With rs1
.ActiveConnection = cn1
.Source = "Select * FROM tblBookings Where RoomCode LIKE '" &
UserForm1.ComboBox1.Text & "%'"
.Open
End With



'===============================================================
'We then create a connection to the spreadsheet with data
'===============================================================

Set ExcelSheet = GetObject(ActiveWorkbook.Path & "\AV.xlsx")

iRowPresent = 2
While Not rs1.EOF

iRowSearch = 2 ' Set the search routine on the spreadsheet to
start at row 2
sCurrent = ExcelSheet.Worksheets("A-Block").Cells(iRowSearch,
1) ' Put the value that we want to compare"

' into sCurrent

While (sCurrent <> "") ' This loop will keep going round
until we pick up an empty celll ie the end of the list
If sCurrent = rs1!RoomCode Then ' if the value that we're
searchin for from the excel
' sheet matches the
current value in the recordset then

Cells(iRowPresent, 6) = rs1!Day
Cells(iRowPresent, 7) = rs1!StartTime
Cells(iRowPresent, 8) = rs1!EndTime
Cells(iRowPresent, 9) = rs1!DurationHours
Cells(iRowPresent, 10) = rs1![Activity Name]
Cells(iRowPresent, 11) = rs1![Activity Size]
Cells(iRowPresent, 12) = ExcelSheet.Worksheets("A-
Block").Cells(iRowSearch, 2) ' Add then add the employee
Cells(iRowPresent, 13) = ExcelSheet.Worksheets("A-
Block").Cells(iRowSearch, 3) ' Add then add the employee

' name to the presenting spreadsheet
iRowPresent = iRowPresent + 1 ' move the pointer up
one to the next row for presentation
End If

iRowSearch = iRowSearch + 1 ' Moves our counter on to the
next row
sCurrent = ExcelSheet.Worksheets("A-
Block").Cells(iRowSearch, 1) ' Put the value that we want to compare

' into sCurrent

Wend
rs1.MoveNext ' get the next record
Wend



' clear up our workspaces
Set rs1 = Nothing
Set cn1 = Nothing
Set exsales = Nothing


Is there a way of querying this statement,

ExcelSheet.Worksheets("A-Block").Cells(iRowSearch, 1)

to multiple worksheets like

ExcelSheet.Worksheets("A-Block","B-Block","C-Block).Cells(iRowSearch,
1)

If not what would be the best way to do this?

thanks for your help in advance

Nick