Nom de fichier .txt en variable dans une formule

Bonjour le forum,

je fais à nouveau appel à votre aide pour un petit problème que je rencontre actuellement,

Petite explication:

J'ai créé une macro qui effectue les opérations suivantes :

1) création d'un tableau dans un nouvel onglet d'un fichier .txt

    Sub saisie()

Application.ScreenUpdating = False

Dim Formule As String, Fichier As String

ChDir "K:\__\__\__\__"
Fichier = Application.GetOpenFilename

     Workbooks.OpenText Filename:=Fichier, Origin:=xlWindows, _
           StartRow:=1, DataType:=xlDelimited, _
           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
           Tab:=True, Semicolon:=True, Comma:=False, Space:=False, _
           Other:=False, FieldInfo:=Array(1, 1)

    Columns("BP:BP").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("BO:BO").Select
    Selection.TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    Range("BP1").Select
    ActiveCell.FormulaR1C1 = "Value"
    Range("BQ1").Select
    ActiveCell.FormulaR1C1 = "Fail"

    Sheets.Add

    Range("A3").Formula = "Nombre de fail"
    Range("A4").Formula = "Station ID"
    Range("B3").Formula = "fail"
    Range("B4").Formula = " FC: Torsion Bar Rate CCW"
    Range("C4").Formula = " FC: Torsion Bar Rate CW"
    Range("D4").Formula = "Total"
    Range("A5").Formula = "SIMATIC4_1"
    Range("A6").Formula = "SIMATIC4"
    Range("A7").Formula = "Total"
    Range("E4").Formula = "Nombre de tests"

2) remplissage de mon tableau (grâce à la formule de Banzai )

     Sub test()
Dim Lg As Long

Fichier = Worksheets(2).Name
Lg = Sheets(Fichier).Range("BA" & Rows.Count).End(xlUp).Row

Range("B5:C6").Formula = "=SUMPRODUCT(--(" & Fichier & "!R2C53:R" & Lg & "C53=RC1)*(" & Fichier & "!R2C69:R" & Lg & "C69=R4C))"

    Range("B7").FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("C7").FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("D5").FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("D6").FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("D7").FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("E5:E6").FormulaR1C1 = "=COUNTIF(" & Fichier & "!C[48],RC[-4])"
    Range("E7").FormulaR1C1 = "=R[-2]C+R[-1]C"

    Sheets(2).Select
    Range("C50").Select
    Selection.Copy
    Sheets(1).Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.NumberFormat = "m/d/yyyy"
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
    End With

End Sub

3) Ouverture d'un classeur "bilan.xls" dans lequel se trouve un tableau, et remplissage du tableau :

Sub bilan()
Dim derligne As Long

Workbooks.Open Filename:="C:\__\__\__\__\Bilan.xls"
Windows("Bilan.xls").Activate

derligne = Range("C65536").End(xlUp).Row + 1
    Range("C" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R7C4"

derligne = Range("D65536").End(xlUp).Row + 1
    Range("D" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R6C4/[" & Fichier & ".txt]Feuil1!R7C4*100"

derligne = Range("E65536").End(xlUp).Row + 1
    Range("E" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R5C4/[" & Fichier & ".txt]Feuil1!R7C4*100"

derligne = Range("F65536").End(xlUp).Row + 1
    Range("F" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R7C2/[" & Fichier & ".txt]Feuil1!R7C4*100"

derligne = Range("G65536").End(xlUp).Row + 1
    Range("G" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R7C3/[" & Fichier & ".txt]Feuil1!R7C4*100"

derligne = Range("B65536").End(xlUp).Row + 1
    Range("B" & derligne).Select
    ActiveCell.FormulaR1C1 = "=[" & Fichier & ".txt]Feuil1!R2C1"

End Sub

C'est là que j'ai mon problème! J'ai remplacé par exemple

=[P2110103.txt]Feuil1!R2C1

par

=[" & Fichier & "]Feuil1!R2C1

ou par

=[" & Fichier & ".txt]Feuil1!R2C1

Mais dans les 2 cas, j'ai une erreur #REF!

Je ne comprends pas ce qui ne va pas!

Si quelqu'un a une idée, je lui en serait très reconnaissant!

Merci d'avance,

Cordialement.

Bonjour

Mes yeux m'abusent peut être mais dans ta macro Sub bilan() je ne vois pas la définition de Fichier

Bonjour Banzai,

Non ta vue est bonne, je ne définis pas Fichier dans ma macro Sub bilan(), mais justement je ne sais pas la syntaxe à utiliser pour dé finir cette variable.

Elle correspond à mon Fichier txt modifié, et c'est ça qui me manque.

Est-ce compréhensible?

Bonjour

sans le fichier

Fichier = ThisWorkbook.Name

Tes formules deviennent

ActiveCell.FormulaR1C1 = "=[" & Fichier & "]Feuil1!R7C4"

Sans le .Txt

Si pas ça

Bonjour Banzai et merci, ça a l'air d'être la solution.

Seulement le "ThisWorkbook" sélectionné n'est pas le bon... cette commande fait pointer ma macro dans mon classeur d'origine (à partir duquel j'ai enregistré ma macro) et non celui que j'ouvre pour piocher les données qui m'intéressent.

J'ai peut être fait une bêtise en enregistrant ma macro dans un fichier "Exemple" dont je ne me sers pas...

EDIT : problème résolu, il fallait utiliser :

Fichier = ActiveWorkbook.Name

Merci