[Excel] External reference to closed workbooks Solved

Question

Hello,

I’m trying to read data from closed workbooks in order to consolidate the information into a single workbook.

My goal is to have a dynamic reference, so I first tried with INDIRECT and CONCATENATE before discovering that INDIRECT is limited to open workbooks.

I therefore found a VBA function LireCellule_ClasseurFerme() on the net to address this issue, but it returns the #VALUE error, and I even noticed that the function works if the workbook is open (which made me smile a little!). Could someone have an explanation that could help me move forward?

Here is the function code:

Function LireCellule_ClasseurFerme( _ 
    Chemin As String, _ 
    Fichier As String, _ 
    Feuille As String, _ 
    Cellule As Variant) As Variant
    Application.Volatile
    Dim Source As Object, Rst As Object, ADOCommand As Object
    Dim Cible As String
    Feuille = Feuille & "$"
    Cible = Cellule.Address(0, 0, xlA1, 0) & ":" & _
            Cellule.Address(0, 0, xlA1, 0)
    Set Source = CreateObject("ADODB.Connection")
    Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Chemin & "\" & Fichier & _
        ";Extended Properties=""Excel 8.0;HDR=No;"";"
    Set ADOCommand = CreateObject("ADODB.Command")
    With ADOCommand
        .ActiveConnection = Source
        .CommandText = "SELECT * FROM [" & Feuille & Cible & "]"
    End With
    Set Rst = CreateObject("ADODB.Recordset")
    '1 = , 3 =
    Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
    Set Rst = Source.Execute("[" & Feuille & Cible & "]")
    LireCellule_ClasseurFerme = Rst(0).Value
    Rst.Close
    Source.Close
    Set Source = Nothing
    Set Rst = Nothing
    Set ADOCommand = Nothing
End Function

I did make sure to enable the Microsoft ActiveX Data Objects Library reference in the Visual Basic tools.

Thanks for your help!

Configuration: Windows XP / Firefox 3.6.12

michel_m · Answer

Hello

The source workbook(s) must meet very specific conditions, notably the target cells must be data lists; i.e., data of the same type in a single column: text, number, date, and formats are not copied.

There is also something odd in this syntax
LireCellule_ClasseurFerme = Rst(0).Value

On the other hand, if you have only a small amount of cell data to copy, you can use the so-called Walkenbach macro, a basic example to adapt (path, file name, cells...)

Sub lire_ferme()
 Dim chemin As String
 chemin = ThisWorkbook.Path
 Range("A3") = ExecuteExcel4Macro("' " & chemin & "\[source.xls]Feuil1'!R1C1")
 Range("B4") = ExecuteExcel4Macro("' " & chemin & "\[source.xls]Feuil1'!R2C2")
 Range("C8") = ExecuteExcel4Macro("' " & chemin & "\[source.xls]Feuil1'!R5C3")
 Range("E9") = ExecuteExcel4Macro("' " & chemin & "\[source.xls]Feuil1'!R7C4")
End Sub

If you are using ADo, attach an excerpt of your source workbook

To attach a file, put the workbook without confidential data as an attachment
https://www.cjoint.com/

and right-click the suggested link then "copy link address" and paste it into your reply

-- 
Michel

Sylvain · Answer

First, thanks for your reply Michel.

When you talk about type, do you mean "format"? If that's the case, the data in my source table are all of the same type in each column. But after all, your remark seems like a good lead since I had already had a formatting issue in this source table; in fact this table is itself exported from the company's software.

Indeed I had already seen Walkenbach's macro but I plan to use my function to later exploit an entire database (for another Excel workbook, this one serving more as a test). Moreover, I would like to stay in the "function" mindset so that users of the workbook can track the cell values.

To be clear, because I know that before trying to code anything you first need to properly define what we want, I would like a function of the indirect.ext type, that is, the indirect function but that works on closed workbooks. Indirect.ext is a function of the MoreFunc add-in, but I can't download it (company policy), and it's impossible to retrieve the VBA code since it is simply coded in C!

Here is an excerpt of my source workbook:

http://cjoint.com/?AJAnhkY3STv

Patrice33740 · Answer

We can read in a closed workbook with a simple formula:

Sub LireFichierFermé() 
    Dim NomFichier As String 
    Dim Chemin As String 
    Dim Formule As String 
    ActiveSheet.Range("A1").ClearContents 
    Chemin = "D:\Temp\" 
    NomFichier = "Classeur_à_lire.xls" 
    Formule = "=' " & Chemin & "[" & NomFichier & "]Feuil1" & "'! " & "A1" 
    ActiveSheet.Range("A1").Formula = Formule 
    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value 
End Sub

michel_m · Answer

A bit complicated XLD....

Const racine As String = "D:\documents\excel\essai\adodemo\" 'a adapter Const onglet As String = "TestBD" Const plage As String = "A1:P1000" Sub compiler() Dim ss_dossier, Cptr As Byte, Chemin As String Dim Source As Object, Requete As Object, texte_SQL As String Dim derlig As Long ss_dossier = Array("ccmA", "ccmB") 'a adapter For Cptr = 0 To UBound(ss_dossier) 'recherche des classeurs dans les sous dossiers Chemin = racine & ss_dossier(Cptr) ChDir Chemin fichier = Dir(Chemin & "\"rapport_nomclient" & "*.xlsx") 'connection au classeur Set Source = CreateObject("ADODB.Connection") With Source .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & fichier & ";Extended Properties=""Excel 12.0;HDR=YES;""' .Open End With ' éxécute l'extraction des données Set Requete = CreateObject("ADODB.Recordset") texte_SQL = "SELECT * FROM [" & onglet & "$" & plage & "] " Set Requete = Source.Execute(texte_SQL) 'restitution If Not Requete.EOF Then derlig = Columns(2).Find("*", , , , , xlPrevious).Row + 1 Sheets(1).Cells(derlig, 2).CopyFromRecordset Requete End If Next Sheets(1).Activate End Sub

https://www.cjoint.com/?3JAtTXYFpVj

Mais à partir de maintenant, je m'abstiendrai de me casser le cul pour aider quelqu'un en allant vérifier s'il n'a pas posé la question sur un autre forum sans prévenir...triste pour ce qui est de l'entraide
Michel

Sylvain · Answer

Indeed, your solution is simple and very effective Patrice, I almost blame myself for not thinking of it, but that’s not what I’m looking for... unless I turn it into a function where I can enter my arguments.
I like the idea of creating my own function directory, because I’m more comfortable with Excel than with VBA and, in addition, my users will immediately be able to see where the cell values come from, because when I’m no longer there (I’m internship), I want them to still be able to manage if they change the folders (thus the addresses) and the workbook configurations (thus the cells). Otherwise my work will have been for nothing!
I’m looking at your solution Michel, even though it’s not a function either, but it will surely help me with processing my database, I’ll try and see how it goes tomorrow!

[Excel] External reference to closed workbooks

5 answers

My keyboard lights up and goes out after 3 seconds

Display issue in google

Gimp apparent edge contour of layers

Xiaomi redmi note 13 pro+ 5g disable ads?

Missing photo library

Tiktok limits suggestive content to 18+ users

Self-invoking js function

Unable to open xsl and xslx files

Copy from excel into google sheets

Invisible text boxes or word forms on pdf