Excel

Extract Data From Closed Workbooks

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

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

Discussion:

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) 

Code:

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 686 times.

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