Hi Damien

Try ADO
http://www.rondebruin.nl/ado.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Damien McBain" wrote in message ...
Hi,

I have a for...next loop which runs through a series of workbook names in a
worksheet, opens a workbook, copies a worksheet into the main workbook then
closes the workbook again.

Is it possible to copy the worksheet from the source workbook into the
destination workbook without opening the source?

Here's the existing code:
=======================
Sub ImportSummaries()
On Error GoTo Hell

Application.ScreenUpdating = False

Dim wbName
Dim PathName
Dim YearPeriod
Dim WSName
Dim wk

YearPeriod = Worksheets("Summary").Range("YearPeriod")
PathName = "C:\OpsReport\" & YearPeriod & "\"
wk = Worksheets("Summary").Range("Weekno")

For Each branch In Worksheets("Tables").Range("BusAreaList")

wbName = branch.Text & " " & YearPeriod & " wk " & wk & ".xls"
WSName = branch.Text

If wsExists(CStr(branch)) Then
Application.DisplayAlerts = False
Worksheets(CStr(branch.Text)).Delete
Application.DisplayAlerts = True

End If

If Not CBool(Len(Dir(CStr(PathName & wbName)))) Then
MsgBox "There's no data to import for " & branch.Text, , "Operating Report"

Else

Workbooks.Open CStr(PathName & wbName)
Workbooks(CStr(wbName)).Worksheets(CStr(branch)).C opy _
After:=Workbooks("Operating Report - Southern Summary.xls"). _
Worksheets(Worksheets.Count)
Workbooks(CStr(wbName)).Close

End If

Next branch

Gout:
Application.ScreenUpdating = True
Exit Sub
Hell:
MsgBox Err.Description, , "Operating Report"
Resume Gout
End Sub
============================

TIA

Damien