Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
Damien,
"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)).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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
Why? Are you trying to make the code run faster?
Yep :-) "Jim Cone" wrote in message ... Damien, "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)).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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
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("YearPeri od") 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.Worksh eets.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 :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
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 "Jim Cone" wrote in message ... 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("YearPeri od") 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.Worksh eets.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 :-) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you copy a worksheet from a workbook wothout opening it?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create copy of sheet tab (excel 2007) when opening workbook | Excel Worksheet Functions | |||
Prevent worksheet from opening in a workbook | Excel Worksheet Functions | |||
Is there away to keep Excel from opening a copy of an already opened workbook and the pages not be at 100% but | New Users to Excel | |||
Copy to different workbook without opening it | Excel Programming | |||
Copy a worksheet without 'opening' workbook | Excel Programming |