Extract Data From Closed Workbooks

Ease of Use


Version tested with

2000, 2003 

Submitted by:



Data from a closed workbook is duplicated in the open workbook. 


Opening very large workbooks may take some time. If you only want a small part of the contents of a very large file you can extract and copy the portion you need without opening the file. (You can also use this procedure when the other workbook is open) 


instructions for use


Option Explicit 'you can extract data from a closed file by using an 'XLM macro. Credit for this technique goes to John 'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm Sub GetDataDemo() Dim FilePath$, Row&, Column&, Address$ 'change constants & FilePath below to suit '*************************************** Const FileName$ = "Book1.xls" Const SheetName$ = "Sheet1" Const NumRows& = 10 Const NumColumns& = 10 FilePath = ActiveWorkbook.Path & "\" '*************************************** DoEvents Application.ScreenUpdating = False If Dir(FilePath & FileName) = Empty Then MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" Exit Sub End If For Row = 1 To NumRows For Column = 1 To NumColumns Address = Cells(Row, Column).Address Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) Columns.AutoFit Next Column Next Row ActiveWindow.DisplayZeros = False End Sub Private Function GetData(Path, File, Sheet, Address) Dim Data$ Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _ Range(Address).Range("A1").Address(, , xlR1C1) GetData = ExecuteExcel4Macro(Data) End Function

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor (VBE)
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select File/Close and Return To Microsoft Excel

Test the code:

  1. Select Tools/Macro/Macros.../GetDataDemo/Run
  2. Alternatively, download the attachment, extract the enclosed folder and open the workbook 'GetData_ClosedBook'. Now follow step 1 above.

Sample File:

GetData.zip 11.25KB 

Approved by mdmackillop

This entry has been viewed 757 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express