Home>

Copy a specific sheet from another Excel file to the currently open Excel sheet
I'm trying to build a program but it doesn't work.

If the target program specifies the file path, file name, and sheet name,
The range of a specific cell (A1: B10) is copied to the current specified sheet, eg sheet3.

The problem is that the original Excel sheet contains a macro,
The program seems to run when the seat is activated
An error will occur.

Therefore, I thought it would be good if I could copy the cell of the sheet directly without opening the file.
If the program directly refers to a specific cell on the sheet, the execution speed is abnormally slow.

Copying the original macro without moving it, or
If i know how to access other file sheets at high speed, please give me a professor.

By the way, the directly referenced program is as follows.

Dim wsPaste As Worksheet
Set wsPaste = ThisWorkbook.Sheets (3)
Dim dataRng As Range

'Spath = full file path
'Sfiles = file name

'Ssheet = sheet name

With wsPaste
Set dataRng = .Range ("A8: g100") 'Range to copy
End With

Dim c As Range

For Each c In dataRng.Cells

c.Formula = _
"= '"&Spath&_
"\ ["&Sfiles&"]"&Ssheet&"'!"&C.Address'

Next
With wsPaste.UsedRange
.Copy
.PasteSpecial xlPasteValues ​​
End With

vba
  • Answer # 1

    Is this the case?
    I don't know if it's fast.
    The operation has not been verified.

    Dim wsPaste As Worksheet
    Set wsPaste = ThisWorkbook.Sheets (3)
    Dim dataRng As Range
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    'Spath = full file path
    'Sfiles = file name
    'Ssheet = sheet name
    Set wbSrc = Workbooks.Open (Spath&"\"&Sfiles)
    Set wsSrc = wbSrc.Worksheets (Ssheet)
    With wsPaste
        Set dataRng = .Range ("A8: g100") 'Range to copy
    End With
    Dim c As Range
    For Each c In dataRng.Cells
        c.Value = wsSrc.Range (c.Address) .Value
    Next
    wbSrc.Close


    .

  • Answer # 2

      

    How to quickly access other file sheets

    In the first place, Excel is always working behind the scenes.
    If you want to refer to the sheet, write the following code so that the processing is about 8.5 times faster.

    'Write at the beginning of sheet update
    Application.Calculation = xlCalculationManual 'Set calculation mode to manual
    Application.EnableEvents = False 'Stop the event
    Application.ScreenUpdating = False 'Stop updating screen display
    'Write at end of sheet update
    Application.Calculation = xlCalculationAutomatic 'Make calculation mode automatic
    Application.EnableEvents = True 'Start event
    Application.ScreenUpdating = True 'Start screen update update

    It doesn't matter, but if macros are embedded, you can delete all macros by changing the file format to .xlsx.