Problème de reconnaissance de type dans une query

Bonjour,

J'essaie de vous exposer la situation le plus clairement possible

Dans le but d'automatiser l'importation de plusieurs set de données triées dans une seule feuille excel, j'ai ecrit un script important les données depuis divers fichiers xlsx. Le problème est que les fichier originaux sont dans un format diff, du coup j'adapte mon script pour importer depuis un fichier diff... tout se passe bien à par le fait que celui-ci ne reconnait plus le type de donnée comme avant (l'enregistrement de macro m'avait mis un type texte et il importait et faisaient les calculs sans soucis)(Run-time error 1004 The name "Source" wasn't recognized...) résultat, je dois mettre le type any dans ma formule, puis faire un CDbl sur toutes mes cellules pour que les formules de ma page fonctionnent.

dans le but de résoudre ce problème j'ai notamment chercher comment déclarer un type double et je ne trouve rien nul part.

Je vous met un extrait du code

ActiveWorkbook.Queries.Add Name:=strna, Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & strPath & """),[Delimiter=""#(tab)"", Columns=56, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _

" #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _

" #""Changed Type"" = Table.TransformColumnTypes" & "(#""Promoted Headers"",{{""A1"", type text}, {""A2"", Int64.Type}, {""A3"", Int64.Type}, {""A4"", Int64.Type}, {""A5"", Int64.Type}, {""A6"", Int64.Type}, {""A7"", type text}, {""" & strDate & " 1"", type Int64}, {""" & strDate & " 2"", type any}, {""" & strDate & " 3"", type any}, {""" & strDate & " 4"", type any}, {""" & strDate & " 5"", type any}, {""" & _

strDate & " 6"", type any}, {""" & strDate & " 7"", type any}, {""" & strDate & " 8"", type any}, {""" & strDate & " 9"", type any}, {""" & strDate & " 10"", type any}, {""" & strDate & " 11"", type any}, {""" & strDate & " 12"", type any}, {""" & strDate & " 13"", type any}, {""" & strDate & " 14"", type any}, {""" & strDate & " 15"", type any}, {""" & strDate & " 16"", type any}, {""" & strDate & " 17"", type any}, {""" & _

strDate & " 18"", type any}, {""" & strDate & " 19"", type any}, {""" & strDate & " 20"", type any}, {""" & strDate & " 21"", type any}, {""" & strDate & " 22"", type any}, {""" & strDate & " 23"", type any}, {""" & strDate & " 24"", type any}, {""" & strDate & " 25"", type any}, {""" & strDate & " 26"", type any}, {""" & strDate & " 27"", type any}, {""" & strDate & " 28"", type any}, {""" & strDate & " 29"", type any}, {""" & _

strDate & " 30"", type any}, {""" & strDate & " 31"", type any}, {""" & strDate & " 32"", type any}, {""" & strDate & " 33"", type any}, {""" & strDate & " 34"", type any}, {""" & strDate & " 35"", type any}, {""" & strDate & " 36"", type any}, {""" & strDate & " 37"", type any}, {""" & strDate & " 38"", type any}, {""" & strDate & " 39"", type any}, {""" & strDate & " 40"", type any}, {""" & strDate & " 41"", type any}, {""" & _

strDate & " 42"", type any}, {""" & strDate & " 43"", type any}, {""" & strDate & " 44"", type any}, {""" & strDate & " 45"", type any}, {""" & strDate & " 46"", type any}, {""" & strDate & " 47"", type any}, {""" & strDate & " 48"", type any}, {"""", type text}})," & Chr(13) & "" & Chr(10) & _

" #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([CNTER] = ""BR09"") and ([RSW] = " & RSW & ") and ([OCC1] = " & CEL & "))," & Chr(13) & "" & Chr(10) & _

" #""Removed Other Columns"" = Table.SelectColumns(#""Filtered Rows"",{""" & strDate & " 1"", """ & strDate & " 2"", """ & strDate & " 3"", """ & strDate & " 4"", """ & strDate & " 5"", """ & strDate & " 6"", """ & strDate & " 7"", """ & strDate & " 8"", """ & strDate & " 9"", """ & strDate & " 10"", """ & strDate & " 11"", """ & strDate & " 12"", """ & strDate & " 13"", """ & strDate & " 14"", """ & strDate & " 15"", """ & _

strDate & " 16"", """ & strDate & " 17"", """ & strDate & " 18"", """ & "" & strDate & " 19"", """ & strDate & " 20"", """ & strDate & " 21"", """ & strDate & " 22"", """ & strDate & " 23"", """ & strDate & " 24"", """ & strDate & " 25"", """ & strDate & " 26"", """ & strDate & " 27"", """ & strDate & " 28"", """ & strDate & " 29"", """ & strDate & " 30"", """ & strDate & " 31"", """ & strDate & " 32"", """ & strDate & " 33"", """ & _

strDate & " 34"", """ & strDate & " 35"", """ & strDate & " 36"", """ & strDate & " 37"", """ & strDate & "" & " 38"", """ & strDate & " 39"", """ & strDate & " 40"", """ & strDate & " 41"", """ & strDate & " 42"", """ & strDate & " 43"", """ & strDate & " 44"", """ & strDate & " 45"", """ & strDate & " 46"", """ & strDate & " 47"", """ & strDate & " 48""})" & Chr(13) & "" & Chr(10) & _

"in" & Chr(13) & "" & Chr(10) & _

" #""Removed Other Columns"""

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strna & ";Extended Properties=""""" _

, Destination:=Range("$H$3")).QueryTable

.CommandType = xlCmdSql

.CommandText = Array("SELECT * FROM [" & strna & "]")

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = False

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = False

.RefreshPeriod = 0

.PreserveColumnInfo = False

.ListObject.DisplayName = "_" & dn

.Refresh BackgroundQuery:=False

End With

je vous donne volontier plus d'informations si nécessaire