[xl-2010] Variable dans Importation de donnée

bonjours a vous
je cherche a utilisé une "variable" dans une formule d'importation
Mon code de base qui marche

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    Workbooks.Open Filename:="D:\PortSave\GabCleeSyncBack\00Taf\000GabValisette\Macro001\Base.xlsx" '           Import Val (3).mdb
    Windows("Base.xlsx").Activate
    Sheets("Feuil1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\gab\Desktop\000GabValisette\Import\Val (3).mdb;Mode=Share Deny Write;E", "xtended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Ty", "pe=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Da", "tabase Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compac", "t=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("urgence")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "D:\PortSave\GabCleeSyncBack\00Taf\000GabValisette\Import\Val (3).mdb"
        .ListObject.DisplayName = "Tableau_Val__1"
        .Refresh BackgroundQuery:=False
    End With
    Application.Run "00MacroRegroupeTotalMdbEnAnneeValisette.xlsm!MacroDivisionParDate"

le but est de remplacer Val 02 par une variable nommer (Station) en debut de code
je pensait a un truc comme sa mais il doit me manqué un truc ....

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Station = "C:\Users\gab\Desktop\000GabValisette\Import\Val (2).mdb"
 
    Workbooks.Open Filename:="D:\PortSave\GabCleeSyncBack\00Taf\000GabValisette\Macro001\Base.xlsx" '           Import Val (2).mdb
    Windows("Base.xlsx").Activate
    Sheets("Feuil1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Station;Mode=Share Deny Write;E", "xtended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Ty", "pe=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Da", "tabase Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compac", "t=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("urgence")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = Station
        .ListObject.DisplayName = "Tableau_Val__1"
        .Refresh BackgroundQuery:=False
    End With

avec ce code un me propose d ouvrir le fichier Station^^
le but final sera de me servir de cette "variable" dans une boucle next for
Merci a vous