Variable et automatisation de connexions à des données externes existantes
P
Bonjour,
Je cherche à connecter plusieurs fichiers excel (1 par onglet) à un seul.
J'utilise DONNEES -> DONNEES EXTERNES -> CONNEXIONS EXISTANTES.
J'ai d'abord enregistré une macro pour une seule connexion qui fonctionne très bien:
Option Explicit
Sub DBLVERIF()
'
' DBLVERIF Macro
'
'
Workbooks("2XX 1T Synthèse bis TRAVAIL.xlsm").Connections.AddFromFile _
"H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\201 1T Synthèse.xlsx"
With ActiveWorkbook.Connections("201 1T Synthèse").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("'201 1T Synthèse$'")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\20" _
, _
"1 1T Synthèse.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False")
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = _
"H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\201 1T Synthèse.xlsx"
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("201 1T Synthèse")
.Name = "201 1T Synthèse"
.Description = ""
End With
With ActiveWorkbook.Connections("201 1T Synthèse").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("'201 1T Synthèse$'")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\20" _
, _
"1 1T Synthèse.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False")
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = _
"H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\201 1T Synthèse.xlsx"
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("201 1T Synthèse")
.Name = "201 1T Synthèse1"
.Description = ""
End With
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\20" _
, _
"1 1T Synthèse.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'201 1T Synthèse$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\201 1T Synthèse.xlsx"
.ListObject.DisplayName = "Tableau__201_1T_Synthèse2"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Tableau__201_1T_Synthèse2").ShowTableStyleRowStripes _
= False
End Sub
Et j'essaie d'automatiser pour n fichiers en modifiant le code:
Option Explicit
Sub DONNEESEXTERNES()
'
' DONNEESEXTERNES Macro
'
'
Dim i As Integer, Feuilles As Long, Vientde As String, MonFichier As Workbook, Chemin As String, Fichier As String
Feuilles = InputBox("Saisie du nombre Feuilles : ", Feuilles)
For i = 1 To Feuilles
Select Case i
Case Is < 10
MsgBox i
Vientde = Sheets("" & "20" & i & "").Range("AI2").Value
Workbooks("2XX 1T Synthèse bis TRAVAIL.xlsm").Connections.AddFromFile _
"" & Vientde & ""
With ActiveWorkbook.Connections("" & "20" & i & " 1T Synthèse" & "").OLEDBConnection
'With ActiveWorkbook.Connections("201 1T Synthèse").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("" & "'20" & i & " 1T Synthèse$'" & "")
'.CommandText = Array("'201 1T Synthèse$'")
.CommandType = xlCmdTable
.Connection = Array( _
"" & "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False" & "")
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = _
"" & Vientde & ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("" & "20" & i & " 1T Synthèse" & "")
.Name = "" & "20" & i & " 1T Synthèse" & ""
.Description = ""
End With
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("" & "'20" & i & " 1T Synthèse$'" & "")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"" & Vientde & ""
.ListObject.DisplayName = "" & "Tableau__20" & i & "_1T_Synthèse2" & ""
'.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("" & "Tableau__20" & i & "_1T_Synthèse2" & "").ShowTableStyleRowStripes = False
'ActiveWorkbook.SaveAs Filename:= _
'"H:\Secretariat\2017-2018\BILAN DES CONSEILS DE CLASSE\2XX 1T Synthèse.xlsm", _
'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Case Is >= 10
Vientde = Sheets("" & "2" & i & "").Range("AI2").Value
Sheets("" & "2" & i & "").Select
Workbooks("2XX 1T Synthèse bis TRAVAIL.xlsm").Connections.AddFromFile _
"" & Vientde & ""
With ActiveWorkbook.Connections("" & "2" & i & " 1T Synthèse" & "").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("" & "'2" & i & " 1T Synthèse$'" & "")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False")
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = _
"" & Vientde & ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("" & "2" & i & " 1T Synthèse" & "")
.Name = "" & "2" & i & " 1T Synthèse1" & ""
.Description = ""
End With
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("" & "'2" & i & " 1T Synthèse$'" & "")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"" & Vientde & ""
.ListObject.DisplayName = "" & "Tableau__2" & i & "_1T_Synthèse2" & """"
'.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("" & "Tableau__2" & i & "_1T_Synthèse2" & "").ShowTableStyleRowStripes = False
End Select
Next
End Sub
Qui plante, erreur 13 avec la ligne:
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;" & "Data Source=" & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False")
J'ai compris que le problème vient de l'utilisation de la variable:
;" & "Data Source=" & Vientde & ";
, essentielle au code mais qui semble mal déclarée ou mal utilisée.
J'ai essayé de la déclarer en
Workbook
, ce qui donne une erreur 9
Dim i As Integer, Feuilles As Long, Class As String, Vientde As Workbook, Chemin As String
Feuilles = InputBox("Saisie du nombre Feuilles : ", Feuilles)
'MsgBox "OK"
For i = 1 To Feuilles
'MsgBox i
'MsgBox Feuilles
Select Case i
Case Is < 10
MsgBox i
Class = Sheets("" & "20" & i & "").Range("AK2").Value
MsgBox Class
Set Vientde = Workbooks("" & Class & "")
MsgBox Vientde
Chemin = Sheets("" & "20" & i & "").Range("AE2").Value
Sheets("" & "20" & i & "").Select
Workbooks("2XX 1T Synthèse bis TRAVAIL.xlsm").Connections.AddFromFile _
"" & Vientde & ""
With ActiveWorkbook.Connections("" & "20" & i & " 1T Synthèse" & "").OLEDBConnection
'With ActiveWorkbook.Connections("201 1T Synthèse").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("" & "'20" & i & " 1T Synthèse$'" & "")
'.CommandText = Array("'201 1T Synthèse$'")
.CommandType = xlCmdTable
.Connection = Array( _
"" & "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Chemin & Vientde & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" _
, _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Je" _
, _
"t OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt D" _
, _
"atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Je" _
, _
"t OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypas" _
, "s ChoiceField Validation=False" & "")
Je tourne en rond la dessus, quelqu'un aurait-il une solution.
MERCI BEAUCOUP D'AVANCE