Copy a specific worksheet from multiple workbooks without openeing the workbook

Question

I have a code below which copies a specific worksheets form all active or open workbooks.

But how to copy the same Worksheet without opening the workbooks, like if we can provide the path in the code , it should be able to pick the given worksheet from all the workbooks form that path.

Below is the code that am currently using.

Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        If wkb.Name <> ThisWorkbook.Name Then
            wkb.Worksheets(sWksName).Copy _
            Before:=ThisWorkbook.Sheets(1)
        End If
    Next

    Set wkb = Nothing
 End Sub

can you confirm if the workbook name is known so that the program can just open the appropriate workbook and copy the required sheet? — gr8tech, Apr 25 '17 at 04:05
Work with ***[Workbooks.Open Method](https://msdn.microsoft.com/en-us/library/office/ff194819.aspx)*** open in the background - Example `wkb Workbooks.Open ("c:\temp\bookname.xls")` also use `Application.ScreenUpdating = False` — 0m3r, Apr 25 '17 at 04:08
@gr8tech Workbook names are different but worksheet name is same in all the workbooks in the given folder. The code should pick the worksheet with specific name( Which is common in all the workbooks in that folder). — AayushmanR, Apr 25 '17 at 04:19
Why do you not want to open the workbooks? This would be so simple with a `Workbooks.Open` followed by various copy/paste operations, but without the `Open` it gets very difficult. — YowE3K, Apr 25 '17 at 04:25
@0m3r Sir, I think your code line will pick a specific sheet from one workbook. But I need one specific worksheet from all the workbooks in a particular folder. So, I was wondering is there any way to give path/ location of all the workbooks so that it can pick a specific sheet from all these workbooks. — AayushmanR, Apr 25 '17 at 04:32
@YowE3K is correct - opening workbook is far easier than working with closed workbooks — 0m3r, Apr 25 '17 at 04:48

0m3r · Answer 1 · 2017-04-25T04:37:51.960

Work with Workbooks.Open Method to open it in in the background, and hide any alerts with Application / ScreenUpdating / EnableEvents / DisplayAlerts

_{Application.ScreenUpdating Property (Excel) Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.}

Example

Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    wkb Workbooks.Open("C:\temp\bookname.xls")

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        wkb.Worksheets(sWksName).Copy _
        Before:=ThisWorkbook.Sheets(1)
    Next

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set wkb = Nothing
 End Sub

Assuming your folder name is C:\Temp\ then Loop until folder returns an empty

Example

    Dim FileName As String
    ' Modify this folder path as needed
    FolderPath = "C:\Temp\"
    ' Call Dir the first time to all Excel files in path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty .
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set wkb = Workbooks.Open(FolderPath & FileName)

        '--->> Do your copy here

        ' Close the source workbook without saving changes.
        wkb.Close savechanges:=False

        ' next file name.
        FileName = Dir()
    Loop

_{Use a Do...Loop structure when you want to repeat a set of statements an indefinite number of times, until a condition is satisfied. If you want to repeat the statements a set number of times, the For...Next Statement is usually a better choice.}

Ulysses · Answer 2 · 2017-04-25T04:47:45.803

0

I am assuming that you don't want to display the opened workbook to the user so the operation is not visible on screen.

If that's the case, you can use the following line before your code

  Application.ScreenUpdating = False

  'open the new/target excel workbook and put all the sheets in there

And following after:

  Application.ScreenUpdating = True

edited Apr 25 '17 at 04:47

answered Apr 25 '17 at 04:25

Ulysses

5,616
7
48
84

In case if you want to use a separate process altogether - you may use vbs coding in invisible app mode http://stackoverflow.com/questions/15908553/how-to-get-workbook-name-of-running-excel-instance-using-vbscript – Ulysses Apr 25 '17 at 04:46

score 0 · Answer 3 · answered Apr 25 '17 at 04:57

It seems then that you have to manually open the workbooks. You can automate this process in VBA as follows;

Sub CopySheets1()
Dim wkb As Workbook
Dim dirPath As String ' Path to the directory with workbooks
dim wkbName as String

dirPath="C:\folder\"

sWksName = "SHEET NAME"

wkbName=Dir(dirPath & "*.xlsx")

For example: dirPath = "C:\folder\" So that the input to the Dir function be like "C:\folder*.xlsx"

Application.DisplayAlerts = False
do while wkbName <> ""
    Set wkb=Application.Workbooks.Open(dirPath & wkbName)
            wkb.Worksheets(sWksName).Copy _
            Before:=ThisWorkbook.Sheets(1)
    wk.Close False
    wkbName = Dir
loop
Application.DisplayAlerts = True


End Sub

Copy a specific worksheet from multiple workbooks without openeing the workbook

3 Answers3