Can you copy a worksheet from a workbook wothout opening it?

[Damien McBain]

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)).Copy _
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

 

[Jim Cone]

Damien,

Re: "copy...without opening the source"
Not that I know of.
Why? Are you trying to make the code run faster?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"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)).Copy _
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

 

[Damien McBain]

Why? Are you trying to make the code run faster?

Yep

 

[Jim Cone]

I modified the code somewhat with the intent of speeding it up,
but I can't test it. Even if the speed is not much different,
hiding the workbook buttons in the Taskbar and displaying a
message in the StatusBar will make it seem faster.

1. All variables were declared and two new ones added.
2. I assumed that the code was in workbook
"Operating Report - Southern Summary.xls" and used "ThisWorkbook"
instead, in several places.
3. Like your line labels.
4. I assume the wsExists function is efficiently constructed.

'--
Sub ImportSummaries()
On Error GoTo Hell
Dim wbName As String
Dim PathName As String
Dim YearPeriod As String
Dim WSName As String
Dim wk As String
Dim branch As Excel.Range
Dim NewBook As Excel.Workbook
Dim blnShow As Boolean

Application.ScreenUpdating = False
blnShow = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
YearPeriod = ThisWorkbook.Worksheets("Summary").Range("YearPeriod")
PathName = "C:\OpsReport\" & YearPeriod & "\"
wk = Worksheets("Summary").Range("Weekno")

For Each branch In Worksheets("Tables").Range("BusAreaList").Cells
wbName = branch.Text & " " & YearPeriod & " wk " & wk & ".xls"
WSName = branch.Text

If wsExists(WSName) Then
Application.DisplayAlerts = False
Worksheets(WSName).Delete
Application.DisplayAlerts = True
End If

If Not CBool(Len(Dir(CStr(PathName & wbName)))) Then
MsgBox "There's no data to import for " & WSName, , "Operating Report"
Else
Application.StatusBar = "Processing " & wbName
Set NewBook = Workbooks.Open(PathName & wbName)
NewBook.Worksheets(WSName).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
NewBook.Close savechanges:=False
End If
Next ' branch

Gout:
Set branch = Nothing
Set NewBook = Nothing
Application.StatusBar = False
Application.ShowWindowsInTaskbar = blnShow
Application.ScreenUpdating = True
Exit Sub
Hell:
MsgBox Err.Description, , "Operating Report"
Resume Gout
End Sub
'--

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Damien McBain"
wrote in message

Why? Are you trying to make the code run faster?

Yep

 

[Damien McBain]

Thanks Jim!

I don't think there's much difference in the speed but the Status bar text
and hiding the taskbar buttons adds a nice professional touch.

cheers,

Damien

 

[Ron de Bruin]

Hi Damien

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