Excel/VBA - Retrieve information from a close workbook

December 2016


[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).

Introduction


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"
.[A1:F10].Copy
Sheets("Feuil1").Range("A1").PasteSpecial xlPasteValues
.[A1:F10].Clear
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"
Else
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"
.[A1].Copy
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()
Loop
End If
End Sub

Downloads


You can download the:

Related :

This document entitled « Excel/VBA - Retrieve information from a close workbook » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.