XL 2016 Composer une référence externe avec INDIRECT()

Aloha

XLDnaute Accro
Bonsoir,

Comme je n'ai plus construit des références externes indirectes avec justement la fonction INDIRECT() depuis belle lurette, je n'y arrive plus.
Je veux donc composer une formule avec SOMMEPROD() avec une référence à une feuille d'un fichier externe, alors que le nom de la feuille en question est inscrit dans une cellule du fichier hébergeant la formule, mettons dans A1
La partie INDIRECT() de la formule sera qch comme ...INDIRECT([Fichier externe]Feuille dont le nom se trouve dans A1)....
J'ai réussi à construire une telle formule qui se réfère à une feuille du même fichier, mais je ne sais pas où placer correctement les apostrophes et les guillemets pour une référence à une feuille d'un fichier externe
Merci d'avance pour votre aide
Aloha
 

Aloha

XLDnaute Accro
Re,
En effet, ça marche, seulement cette formule n'est pas tout à fait adaptée à mes besoins: elle se réfère à une feuille déterminée, alors qu'elle doit prendre le nom de la feuille du contenu d'une cellule.
D'ailleurs, la fonction INDIRECT est en réalité superflue dans cet exemple: ='[nom du fichier.xlsx]nom onglet'!$A$1 donne le même résultat.
A+
Aloha
 

Aloha

XLDnaute Accro
Re,
en étudiant des solutions trouvées sur le web et en faisant maints essais, j'ai enfin trouvé la formule qui fonctionne, du genre:
INDIRECT("[Fichier_source.xlsx]"&B1&"!A1"), où le nom de l'onglet se trouve dans la cellule B1.

Mais, mais, un bémol: la fonction INDIRECT fonctionne uniquement lorsque le fichier source est ouvert.
Il me faut donc une autre fonction qui livre aussi des résultats lorsque le fichier source est fermé.
Bonne nuit
Aloha
 

Staple1600

XLDnaute Barbatruc
Bonsoir le fil, le forum

A défaut d'avoir retrouvé trace dans mes archives d'INDIRECT.EXT (de Laurent Longre)
Un substitut VBA (une fonction personnalisée) issue de mes archives anglophones
VB:
'------------------------------------
'Extended INDIRECT Function v1.0
'------------------------------------
'Copyright (c) 2009 Wilson So.
'E-mail: shwskm@yahoo.com.hk
'------------------------------------
'Credits:
'- Designed and written by Wilson So.
'- The 'CreateObject("Excel.Application")' trick was inspired by Harlan Grove's PULL function source code.
'------------------------------------
'This is an open source. You can freely redistribute and modify it, but please kindly give credit to the contributers.
'Please also kindly report any bugs/suggestions through e-mail or in the forums where I posted it.
'------------------------------------
'How to use:
'- Basically same as INDIRECT() in Excel - the same concept for the ref_text parameter.
'- To update the static memory for a particular reference,
'  type TRUE in the second parameter (just one of the IndirectEx() containing that reference)
'  and calculate it once.
'------------------------------------
'Features:
'- You can refer to the closed workbook data.
'- The retrieved closed workbook data will be stored in the static memory,
'  so in the next time, the closed workbook will not be opened again for fast retrieve.
'- A range instead of an array will be returned if the path is omitted in the ref_text,
'  so it still works fine if the user refers to an enormous array, e.g. "Sheet1!1:65536".
'- You can use it inside INDEX(), VLOOKUP(), MATCH() etc.
'- You can use it with OFFSET(), but only for opened workbook data.
'- The procedure will not blindly retrieve all the data as requested;
'  it will not retrieve data beyond the "Ctrl + End" cell, in order to keep the memory as small as possible.
'- #NUM! will be returned in case of lack of memory.
'- #REF! will be returned in case of a wrong path.
'- #VALUE! will be returned in case of other errors.
'------------------------------------
'Known issues:
'- Due to the use of SpecialCells(), #VALUE! will be returned if the worksheet for a closed workbook is protected.
'------------------------------------

Function IndirectEx(ref_text As String, Optional refresh_memory As Boolean = False) As Variant
    On Error GoTo ClearObject

    Dim RefName As String
    Dim SheetName As String
    Dim WBName As String
    Dim FolderName As String
  
    Dim vExcel As Object
    Dim vWB As Workbook
  
    Static dbOutput() As Variant
    Static dbKey() As String
    Static dbTotalOutput As Integer
    Dim dbIndex As Integer
  
    Dim UserEndRow As Long, UserEndCol As Integer
    Dim RealEndRow As Long, RealEndCol As Integer
    Dim EndRow As Long, EndCol As Integer
    Dim RangeHeight As Long, RangeWidth As Integer
  
    GetNames ref_text, RefName, SheetName, WBName, FolderName
  
    If dbTotalOutput = 0 Then
        ReDim dbOutput(1 To 1) As Variant
        ReDim dbKey(1 To 1) As String
    End If
  
    For i = 1 To dbTotalOutput
        If dbKey(i) = FolderName & WBName & "!" & SheetName & "!" & RefName Then
            dbIndex = i
        End If
    Next
  
    If dbIndex = 0 Or refresh_memory Then
        If dbIndex = 0 Then
            dbTotalOutput = dbTotalOutput + 1
            dbIndex = dbTotalOutput
            ReDim Preserve dbOutput(1 To dbTotalOutput) As Variant
            ReDim Preserve dbKey(1 To dbTotalOutput) As String
            dbKey(dbIndex) = FolderName & WBName & "!" & SheetName & "!" & RefName
        End If
        If FolderName = "" Then
            Set dbOutput(dbIndex) = Workbooks(WBName).Worksheets(SheetName).Range(RefName)
        ElseIf Dir(FolderName & WBName) <> "" Then
            Set vExcel = CreateObject("Excel.Application")
            Set vWB = vExcel.Workbooks.Open(FolderName & WBName)
            With vWB.Sheets(SheetName)
                On Error GoTo ClearObject
                UserEndRow = .Range(RefName).Row + .Range(RefName).Rows.Count - 1
                UserEndCol = .Range(RefName).Column + .Range(RefName).Columns.Count - 1
                RealEndRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
                RealEndCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
                EndRow = IIf(UserEndRow < RealEndRow, UserEndRow, RealEndRow)
                EndCol = IIf(UserEndCol < RealEndCol, UserEndCol, RealEndCol)
                RangeHeight = EndRow - .Range(RefName).Row + 1
                RangeWidth = EndCol - .Range(RefName).Column + 1
                On Error Resume Next
                dbOutput(dbIndex) = .Range(RefName).Resize(RangeHeight, RangeWidth).Value
                If Err.Number <> 0 Then
                    IndirectEx = CVErr(xlErrNum)
                    GoTo ClearObject
                End If
            End With
            On Error GoTo ClearObject
            vWB.Close False
            vExcel.Quit
            Set vExcel = Nothing
        Else
            IndirectEx = CVErr(xlErrRef)
            Exit Function
        End If
    End If
  
    If TypeOf dbOutput(dbIndex) Is Range Then
        Set IndirectEx = dbOutput(dbIndex)
    Else
        IndirectEx = dbOutput(dbIndex)
    End If

    Exit Function
  
ClearObject:
    On Error Resume Next
    If Not (vExcel Is Nothing) Then
        vWB.Close False
        vExcel.Quit
        Set vExcel = Nothing
    End If
End Function

Private Sub GetNames(ByVal ref_text As String, ByRef RefName As String, ByRef SheetName As String, ByRef WBName As String, ByRef FolderName As String)
    Dim P_e As Integer
    Dim P_b1 As Integer
    Dim P_b2 As Integer
    Dim P_s As Integer
  
    P_e = InStr(1, ref_text, "!")
    P_b1 = InStr(1, ref_text, "[")
    P_b2 = InStr(1, ref_text, "]")
    P_s = InStr(1, ref_text, ":\")
  
    If P_e = 0 Then
        RefName = ref_text
    Else
        RefName = Right$(ref_text, Len(ref_text) - P_e)
    End If
    RefName = Replace$(RefName, "$", "")
  
    If P_e = 0 Then
        SheetName = Application.Caller.Parent.Name
    ElseIf P_b1 = 0 Then
        SheetName = Left$(ref_text, P_e - 1)
    Else
        SheetName = Mid$(ref_text, P_b2 + 1, P_e - P_b2 - 1)
    End If
    SheetName = Replace$(SheetName, "'", "")
  
    If P_b1 = 0 Then
        WBName = Application.Caller.Parent.Parent.Name
    Else
        WBName = Mid$(ref_text, P_b1 + 1, P_b2 - P_b1 - 1)
    End If
  
    If P_s = 0 Then
        FolderName = ""
    Else
        FolderName = Left$(ref_text, P_b1 - 1)
    End If
    If Left$(FolderName, 1) = "'" Then FolderName = Right$(FolderName, Len(FolderName) - 1)
End Sub
 

Aloha

XLDnaute Accro
Bonjour,
Merci beaucoup.
Mais, somme toute, je me demande s'il n'est pas plus simple d'ouvrir le fichier source et si je veux que les résultats ne soient pas furtiles, je peux remplacer les formules par leur valeur et remettre les formules par code VBA (enregistré) à l'ouverture du fichier, ou bien d'ouvrir systématiquement le fichier source manuellement avant d'ouvrir le fichier contenant les formules, ce que je ne risque pas d'oublier puisque je le remarquerai tout de suite au résultat des formules.
Bonne journée
Aloha
 

Discussions similaires