Copy data from a closed workbook without opening it

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,147
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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.

VBA Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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

VBA Code:
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

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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!

VBA Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Or to stay in the spirit of t0ny84
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,012
Members
416,005
Latest member
judi slot terbaik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top