Astra

Excel VBA: Copy Data from Another Workbook Without Opening

Estimated reading: 5 minutes

After reading this article, you will be familiar with the VBA codes to copy data from another workbook without opening it. Here, we will use multiple methods to copy data from another workbook.

Code 01: Copy Data from Another Workbook Using Workbook Objects

Following the VBA code enables a user to copy a specified range from a specified path. 

Sub Copy_Data_From_Another_Workbook_Using_Workbook_Method()
    Dim sourceFilePath As String
    Dim sourceFileName As String
    Dim sourceWorksheetName As String
    Dim sourceRangeAddress As String
    Dim targetRange As Range
    Dim data As Variant
    
    'Set the file path and sheet name of the source workbook
    sourceFilePath = "C:\Users\USER\Downloads\Solved Excel.xlsx"
    sourceFileName = Mid(sourceFilePath, InStrRev(sourceFilePath, "\") + 1)
    sourceWorksheetName = "Sheet1"
    sourceRangeAddress = "B1:C9"
    
    'Read the data from the source workbook
    data = Workbooks.Open(sourceFilePath, False, True).Worksheets(sourceWorksheetName).Range(sourceRangeAddress).Value
    
    'Set the target range where you want to copy the data
    Set targetRange = ThisWorkbook.Worksheets(1).Range(sourceRangeAddress)
    
    'Copy the data to the target range
    targetRange.Value = data
    
    'Close the source workbook without saving changes
    Workbooks(sourceFileName).Close False
    
    'Save the current workbook
    ThisWorkbook.Save
    
    'Clean up
    Set targetRange = Nothing
End Sub

Note: To run this code, you should check the following references.

excel vba copy data from another workbook without opening

Result:

  • Suppose you want to copy the data range B1:C9 from a workbook located in the path C:\Users\USER\Downloads\Solved Excel.xlsx. 
  • After running the code by pressing F5, the data will be copied to the current sheet. 
excel vba copy data from another workbook without opening1

Code 02: Copy Data from Another Workbook Without Opening Using the FileSystemObject Method

Sub Copy_Data_From_Another_Workbook_Using_FSO()
    Dim file_sys_obj As Object
    Dim sourceFilePath As String
    Dim sourceWorksheetName As String
    Dim sourceRangeAddress As String
    Dim targetRange As Range
    Dim data As Variant
    
    'Create a FileSystemObject
    Set file_sys_obj = CreateObject("Scripting.FileSystemObject")
    
    'Set the file path and sheet name of the source workbook
    sourceFilePath = "C:\Users\USER\Downloads\Solved Excel.xlsx"
    sourceWorksheetName = "Sheet1"
    sourceRangeAddress = "B1:C9"
    
    'Check if the file exists
    If Not file_sys_obj.FileExists(sourceFilePath) Then
        MsgBox "File not found."
        Exit Sub
    End If
    
    'Set the target range where you want to copy the data
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    'Read the data from the source workbook
    With Workbooks.Open(sourceFilePath)
        data = .Worksheets(sourceWorksheetName).Range(sourceRangeAddress).Value
        .Close False
    End With
    
    'Copy the data to the target range
    targetRange.Resize(UBound(data, 1), UBound(data, 2)).Value = data
    
    'Save the current workbook
    ThisWorkbook.Save
    
    'Clean up
    Set targetRange = Nothing
    Set file_sys_obj = Nothing
End Sub

Code 03: Copy Data from Another Workbook Using the ADO (ActiveX Data Object)

Sub Copy_Data_From_Another_Workbook_Using_ActiveX_Obj()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim filePath As String
    Dim sheetName As String
    Dim targetRange As Range
    
    'Set the file path and sheet name of the source workbook
    filePath = "C:\Users\USER\Downloads\Solved Excel.xlsx"
    sheetName = "Sheet1"
    
    'Set the target range where you want to copy the data
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    'Create a connection to the source workbook using ADO
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=Yes"""
    conn.Open
    
    'Execute a SQL query to select the data from the source workbook
    sql = "SELECT * FROM [" & sheetName & "$]"
    Set rs = conn.Execute(sql)
    
    'Copy the data to the target range
    targetRange.CopyFromRecordset rs
    
    'Save the current workbook
    ThisWorkbook.Save
    
    'Close the connection and clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

Example: Copy the Selected Data Range from the Selected Workbook with Formatting. 

This code will allow the user to select any Excel file as well as a range from the selected Excel file. Then paste the range to the current workbook. 

Sub Copy_Data_With_Formatting_And_Fit_Width()
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range
    
    'Open the source workbook and worksheet
    Set sourceWorkbook = Application.Workbooks.Open(Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , "Select Source Workbook"))
    Set sourceWorksheet = sourceWorkbook.ActiveSheet
    
    'Select the source range to copy
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the data range to copy", "Select Range", Type:=8)
    On Error GoTo 0
    If sourceRange Is Nothing Then Exit Sub
    
    'Copy the source range with formatting
    sourceRange.Copy
    Set targetRange = ThisWorkbook.Worksheets(1).Range("B1")
    targetRange.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    'Fit the column width to the copied data
    targetRange.CurrentRegion.Columns.AutoFit
    
    'Close the source workbook without saving changes
    sourceWorkbook.Close SaveChanges:=False
    
    'Save the current workbook
    ThisWorkbook.Save
    
End Sub

Result: After running the code, a file picker will open to allow a user to select an Excel file.

excel vba copy data from another workbook without opening2

After selecting the file, click on the “Open” button. An input box will appear to enter the selected range of cells. 

excel vba copy data from another workbook without opening3

Then click on OK. The selected range with formatting will be copied to the current workbook.

excel vba copy data from another workbook without opening4

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics
SOLVED EXCEL
Scroll to Top