Copy data from a closed workbook without opening it

[dpaton05]

Can someone please let me know how to copy data from a closed workbook without opening it?

I also want to use a constant to store the workbook name called QuoteTool.

The range to copy is A3:E12 of sheet2 within QuoteTool and it needs to be copied to A33:E42 of sheet2 of the workbook that is calling the procedure.

 

[t0ny84]

Hi dpaton05, I don't believe you can do this without opening the spreadsheet.

If you put the following code in the Workbook that you are wanting to copy the information to and assign it to a button. This would open QuoteTools in the background copy A33:E42 from Sheet2 and paste it into the current Workbook on Sheet1.

Sub Copy()
Dim x As Workbook
Application.ScreenUpdating = False

Set x = Workbooks.Open("C:\Excel\Workbooks\QuoteTool.xlsx") 'Change the workbook path from where you want to copy

'Below
' Sheet2 = Sheet in QuoteTools
' Sheet1 is the sheet you are copying the data to.

With x.Sheets("Sheet2").UsedRange
 ThisWorkbook.Sheets("Sheet1").Range("A3:E12").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With

x.Close

Application.ScreenUpdating = True

End Sub

 

[jolivanes]

May I ask what the big deal of opening a workbook is?
Is there a code running on opening?

You can do it with a formula if it needs to be done that way.
Change references where required.

Sub Get_Data()
With Sheets(2)
     With .Range("A33:E42")
        .Formula = "='C:\Folder Name\[QuoteTool.xlsm]Sheet" & 2 & "'!R[-30]C"
'        .Value = .Value    '<----- Optional
    End With
End With
End Sub

 

[t0ny84]

Sorry I didn't realise you wanted the cells to copy to another location, using my code the copy and paste component seems to break.
This site might help with the copy paste portion of the code - Excel VBA Copy - The Complete Guide to Copying Data - Excel Macro Mastery

Sub Copy()
Dim QuoteTools As Workbook

Application.ScreenUpdating = False

Set QuoteTools = Workbooks.Open("C:\Excel\Workbooks\QuoteTool.xlsx") 'Change the workbook path from where you want to copy

' Paste In Copy \ Paste Code

QuoteTools.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

 

[t0ny84]

Sorry for the multiple posts it won't let me edit the other posts above, I just realised why the copy\paste wasn't working - I didn't have a Sheet2 in my test which is why it wasn't working!

Sub CopyQuoteToolsData()
Dim x As Workbook  'x = QuoteTools Workbook

Application.ScreenUpdating = False 'Allow the below code to run without changes happening on screen.

Set x = Workbooks.Open("C:\Excel\Workbooks\QuoteTool.xlsx") 'Change the workbook path here to where the QuoteTools workbook is stored.

With x.Sheets("Sheet2").Range("A3:E12") 'Copies the cells from QuoteTools.xlsx Rang A3:E12
ThisWorkbook.Sheets("Sheet2").Range("A33:E42").Resize( _     'Pastes into A33:E42 Cells on Sheet2 of Workbook VB Code is running.
    .Rows.Count, .Columns.Count) = .Value
        End With

x.Close SaveChanges:=False 'Closes QuoteTools without saving any changes and also no prompt to save.
Application.ScreenUpdating = True 'Turns changes to being visible again.
End Sub

Also instead of below:
ThisWorkbook.Sheets("Sheet2").Range("A33:E42").Resize( _
.Rows.Count, .Columns.Count) = .Value

You can use - ThisWorkbook.Sheets("Sheet2").Range("A33:E42") = .Value

 

[jolivanes]

Or to stay in the spirit of t0ny84

Sub Maybe_A()
Dim wb2 As Workbook
Application.ScreenUpdating = False
    Set wb2 = Workbooks.Open("C:\Folder Name\QuoteTool.xlsm")    '<---- Change as required
        wb2.Sheets(2).Range("A3:E12").Copy ThisWorkbook.Sheets(2).Range("A33")
    wb2.Close False    '<---- Close the workbook you opened without saving
Application.ScreenUpdating = True
End Sub

Just values

Sub Maybe_B()
Dim wb2 As Workbook
Application.ScreenUpdating = False
    Set wb2 = Workbooks.Open("C:\Folder Name\QuoteTool.xlsm")    '<---- Change as required
        ThisWorkbook.Sheets(2).Range("A33:E42").Value = wb2.Sheets(2).Range("A3:E12").Value
    wb2.Close False    '<---- Close the workbook you opened without saving
Application.ScreenUpdating = True
End Sub

 

[SantanaKRE8s]

How ccan I do the same codde but only transfer over certain columns

 

[MartinS]

For anyone trying to do what the OP wanted, i.e. read data from a workbook without opening it, I've found a solution in another post that I have updated and found works really well for a single cell.

Function GetExcelData(sFilePath As String, sSheetName As String, sRangeAddress As String)
    'Declare procedure level variables
    Dim RangeData$
    Dim oFS         As Object
    Dim sFileName As String
    'Create a late bonding reference to the FSO
    Set oFS = CreateObject("Scripting.FileSystemObject")
    'Get the file path
    sFileName = oFS.GetBaseName(sFilePath) & "." & oFS.GetExtensionName(sFilePath)
    'Define the full location of range to read from
    RangeData$ = "'" & oFS.GetParentFolderName(sFilePath) & "\[" & sFileName & "]" & sSheetName & "'!" & Range(sRangeAddress).Range("A1").Address(, , xlR1C1)
    'Return the value to the function
    GetExcelData = ExecuteExcel4Macro(RangeData$)
End Function

So usage would be:

FileData$ = GetExcelData("C:\Temp\MyTestFile.xlsx", "Config", "K2")

Hope someone finds this useful.