Excel/VBA - Retrieve information from a close workbook

Ask a question

[Excel-VBA] Read in a closed workbook

There are several methods under VBA , to read data from a Excel workbook, without opening the later. Most of these methods are known for their complexity, but however, there is a little trick to get around this, without using OLE DB connection (Read-Only mode).


We will try to retrieve information to our "Recap" workbook. The information is located in the following range of cells "A1: F10" of "Sheet1" from "source" workbook ,located in the "C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\" directory
  • First open the workbook and enter "Pijaku" in Sheet1, from cell A1 to cell F10 and save it as "source.xls" in the "C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\"
  • Let us close this workbook.
  • Open a second workbook and save it as "Recap.xls" (on the desktop).

The method by Excel:
In your Recap.xls workbook:
Insert a defined name.
  • Menu: Insert
  • Choice: Name
  • Click on: Set
  • Names in workbook => type: range
  • Refers to => Enter = 'C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\[source.xls]Feuil1'!$A$1:$F$10
  • Click on Add.
  • In your workbook, enter the formula in A1: =range
  • and validate.
  • Stretch this formula from A1 to F10 ...

VBA code

Sub ImporterDonneesSansOuvrir()  
Dim Chemin As String, Fichier As String
Chemin = "C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\"
Fichier = "source.xls"
ThisWorkbook.Names.Add "plage", _
RefersTo:="='" & Chemin & "[" & Fichier & "]Feuil1'!$A$1:$F$10"
With Sheets("Feuil2")
.[A1:F10] = "=plage"
Sheets("Feuil1").Range("A1").PasteSpecial xlPasteValues
End With
End Sub

Note that

Above all, make sure the path to the directory, spelling are corrent. Also check if "\" is present between directory path and name of the workbook.

Example of use

This code loops through all the workbooks in a directory and extract the contents of cell A1 (sheet1), in this case for example, a date.

Option Explicit

Sub ImporterDates()
Dim objShell As Object, objFolder As Object
Dim Chemin As String, fichier As String

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(&H0&, "Choisir un répertoire", &H1&)

If objFolder Is Nothing Then
MsgBox "Abandon opérateur", vbCritical, "Annulation"
Columns(1).NumberFormat = "m/d/yyyy"
Chemin = objFolder.ParentFolder.ParseName(objFolder.Title).Path & "\"
[B1] = Chemin
fichier = Dir(Chemin & "*.xls")
Do While Len(fichier) > 0
If fichier <> ThisWorkbook.Name Then
ThisWorkbook.Names.Add "Plage", _
RefersTo:="='" & Chemin & "[" & fichier & "]Feuil1'!$A$1"
With Sheets("Feuil2")
.[A1] = "=Plage"
Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = fichier
End With
End If
fichier = Dir()
End If
End Sub


You can download the:
Jean-François Pillou

Jean-François Pillou - Founder of CCM
Better known as Jeff, Jean-François Pillou is the founder of CommentCaMarche.net. He is also CEO of CCM Benchmark and digital director at the Figaro Group.

Learn more about the CCM team