Equivalent de INDIRECT pour un fichier fermé SANS MACRO

Fermé
sodwac
Messages postés
7
Date d'inscription
mardi 23 avril 2013
Statut
Membre
Dernière intervention
13 mai 2013
- 13 mai 2013 à 16:42
 CHU - 28 nov. 2016 à 17:15
Bonjour,

Je me permets de solliciter votre aide pour un sujet dont je ne trouve pas la solution.

Même si le problème est bien résumé dans le titre, je vous donne un peu plus de détail.

Dans le fichier Fichier2, j'ai une formule de type INDIRECT("'[fichier1]Nom_Feuille!"&ADRESSE(LIGNE();COLONNE()). Les données se mettent à jour parfaitement lorsque Fichier1 est ouvert mais malheuresement pas lorsqu'il est fermé.

Je dois absolument garder dans ma formule le ADRESSE(LIGNE();COLONNE()) et je ne peux pas utiliser de macro.

Je vous remercie d'avance pour vos remarques.

Cdlt,

5odwac
A voir également:

3 réponses

Patrice33740
Messages postés
8549
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
7 mai 2022
1 753
13 mai 2013 à 21:47
Au lieu d'utiliser INDIRECT, tu peux utiliser INDEX avec le chemin complet du fichier, par exemple :

=INDEX('D:\Temp\[Fichier1.xls]Nom_feuille'!$A$1:$IV$65536;LIGNE();COLONNE())
5
perduesurexcel
24 mars 2015 à 19:14
Quand le nom du fichier est variable, comment faire ?
0
@Perduesurexcel si vous avez trouvé la réponse, c'est exactement le problème que j'essaie actuellement de résoudre ! :)
0
Bon j'ai trouvé !
Un certain Wilson So a développé une fonction permettant d'agir exactement comment INDIRECT, mais également avec des fichiers Excel fermés !
Cette fonction est appelée IndirectExt, je colle le code ci-dessous.





'------------------------------------
'Extended INDIRECT Function v1.0
'------------------------------------
'Copyright (c) 2009 Wilson So.
'E-mail: ***@***
'------------------------------------
'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
0
Eléonore > Guillaume
24 août 2016 à 14:11
Bonjour,
le code ne semble pas fonctionner car la fonction Indirectex renvoie la valeur 0 au lieu de la valeur demandée (j'ai fait plusieurs essais).
Quelqu'un a t il une idée ?
Merci
0