Queries.Add Name

Jefekoi

XLDnaute Junior
Bonjour,

Je cale, j'essaie de lui dire que le lien de mon fichier se trouve à un certain endroit (PathName & Filename) mais ça ne marche pas :(

Pourriez vous m'aider

Merci


Code:
  PathName = Sheets("Accueil").Range("B1").Value
  Filename = Sheets("Accueil").Range("C1").Value

ActiveWorkbook.Queries.Add Name:="Report (3)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(PathName & Filename), null, ....
 

Jefekoi

XLDnaute Junior
Merci Job ça ne marche pas :(
Je me suis acheté un portable PC avec Excel et du coup je ne fais plus sur MAC :)

Voici ce qui marche
Code:
ActiveWorkbook.Queries.Add Name:="Report2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\Eric\Desktop\VPG\D2602-723170-01MH-1500.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Report_Sheet = Source{[Item=""Report"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Report_Sheet,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type any}, {""Column4"", type text}, {""Column5" & _
        """, type any}, {""Column6"", type any}, {""Column7"", type any}, {""Column8"", type any}, {""Column9"", type any}, {""Column10"", type any}, {""Column11"", type any}, {""Column12"", type any}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type any}, {""Column17"", type text}, {""Column18"", type text}, {""Column19""," & _
        " type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""

Et voici ce qui ne marche pas :

Code:
  PathName = "C:\Users\Eric\Desktop\VPG\D2602-723170-01MH-1500.xlsx"
' C:\Users\Eric\Desktop\VPG\D2602-723170-01MH-1500.xlsx
Effacer
    ActiveWorkbook.Queries.Add Name:="Report2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(PathName), null, true)," & Chr(13) & "" & Chr(10) & "    Report_Sheet = Source{[Item=""Report"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Report_Sheet,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type any}, {""Column4"", type text}, {""Column5" & _
        """, type any}, {""Column6"", type any}, {""Column7"", type any}, {""Column8"", type any}, {""Column9"", type any}, {""Column10"", type any}, {""Column11"", type any}, {""Column12"", type any}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type any}, {""Column17"", type text}, {""Column18"", type text}, {""Column19""," & _
        " type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""

Il faut voir au niveau :
Code:
Source = Excel.Workbook(File.Contents(


Windows 10 ..
 

job75

XLDnaute Barbatruc
Bonjour jefekoi, le forum,

Pour que le 2ème code fonctionne il faut concaténer avec des "&" la variable PathName dans la chaîne :
Code:
PathName = "C:\Users\Eric\Desktop\VPG\D2602-723170-01MH-1500.xlsx"
ActiveWorkbook.Queries.Add Name:="Report2", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" & PathName & """), null, true)," & Chr(13) & "" & Chr(10) & "    Report_Sheet = Source{[Item=""Report"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Report_Sheet,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type any}, {""Column4"", type text}, {""Column5" & _
    """, type any}, {""Column6"", type any}, {""Column7"", type any}, {""Column8"", type any}, {""Column9"", type any}, {""Column10"", type any}, {""Column11"", type any}, {""Column12"", type any}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type any}, {""Column17"", type text}, {""Column18"", type text}, {""Column19""," & _
    " type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
Bonne journée.
 

Jefekoi

XLDnaute Junior
Salut,

Désole de ne pas avoir répondu avant , merci ça marche je suis juste en train de chercher une condition pour vérifier si ma table existe ou pas.

Code:
PathNames = Sheets("Accueil").Range("A1").Value
Name = Sheets("Accueil").Range("A2").Value
PorteName = Mid(Name, 1, 5)

If TableExists(PorteName) Then

Pas facile à trouver
 

Discussions similaires

Réponses
22
Affichages
3 K
Réponses
4
Affichages
2 K

Statistiques des forums

Discussions
311 724
Messages
2 081 936
Membres
101 844
dernier inscrit
pktla