Arrête macro si cellule vide

Le 08/01/2023 à 14:40

Bonjour,

Je souhaiterai faire arrête la macro si la cellule interoger lien1, lien2 , lien3 (A2,A3,A4) est vide.

Sub Macro1()
'
' Macro1 Macro
'
lien1 = Chr(34) & Sheets("feuil1").Range("A2") & Chr(34)
nomonglet = Chr(34) & Sheets("feuil1").Range("B2") & Chr(34)

    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien1 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
        ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False

    End With

' Interoger A3 si vide arrete la macro

    lien2 = Chr(34) & Sheets("feuil1").Range("A3") & Chr(34)
    nomonglet = Chr(34) & Sheets("feuil1").Range("B3") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien2 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__2"
        .Refresh BackgroundQuery:=False
    End With

     lien3 = Chr(34) & Sheets("feuil1").Range("A4") & Chr(34)
     nomonglet = Chr(34) & Sheets("feuil1").Range("B4") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (3)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien3 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (3)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (3)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__3"
        .Refresh BackgroundQuery:=False
            End With

    lien4 = Chr(34) & Sheets("feuil1").Range("A5") & Chr(34)
    nomonglet = Chr(34) & Sheets("feuil1").Range("B5") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien4 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (4)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (4)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__4"
        .Refresh BackgroundQuery:=False
    End With
    lien5 = Chr(34) & Sheets("feuil1").Range("A6") & Chr(34)
    nomonglet = Chr(34) & Sheets("feuil1").Range("B6") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (5)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien5 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (5)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (5)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__5"
        .Refresh BackgroundQuery:=False
    End With
lien6 = Chr(34) & Sheets("feuil1").Range("A7") & Chr(34)
nomonglet = Chr(34) & Sheets("feuil1").Range("B7") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (6)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien6 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (6)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (6)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__6"
        .Refresh BackgroundQuery:=False
    End With

    lien7 = Chr(34) & Sheets("feuil1").Range("A8") & Chr(34)
    nomonglet = Chr(34) & Sheets("feuil1").Range("B8") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (7)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien7 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (7)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (7)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__7"
        .Refresh BackgroundQuery:=False
    End With

        lien8 = Chr(34) & Sheets("feuil1").Range("A9") & Chr(34)
        nomonglet = Chr(34) & Sheets("feuil1").Range("B9") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (8)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien8 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (8)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (8)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__8"
        .Refresh BackgroundQuery:=False
    End With

        lien9 = Chr(34) & Sheets("feuil1").Range("A10") & Chr(34)
        nomonglet = Chr(34) & Sheets("feuil1").Range("B10") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (9)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien9 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (9)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (9)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__9"
        .Refresh BackgroundQuery:=False
    End With

          lien10 = Chr(34) & Sheets("feuil1").Range("A11") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B11") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (10)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & lien10 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (10)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (10)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__10"
        .Refresh BackgroundQuery:=False
    End With

          lien11 = Chr(34) & Sheets("feuil1").Range("A12") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B12") & Chr(34)

     ActiveWorkbook.Queries.Add Name:="Table 0 (11)", Formula:= _
        "let" & Chr(13) & "" & Chr(11) & "    Source = Web.Page(Web.Contents(" & lien11 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (11)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (11)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__11"
        .Refresh BackgroundQuery:=False
    End With

          lien12 = Chr(34) & Sheets("feuil1").Range("A13") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B13") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (12)", Formula:= _
        "let" & Chr(13) & "" & Chr(11) & "    Source = Web.Page(Web.Contents(" & lien12 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (12)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (12)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__12"
        .Refresh BackgroundQuery:=False
    End With

          lien13 = Chr(34) & Sheets("feuil1").Range("A14") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B14") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (13)", Formula:= _
        "let" & Chr(13) & "" & Chr(11) & "    Source = Web.Page(Web.Contents(" & lien13 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (13)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (13)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__13"
        .Refresh BackgroundQuery:=False
    End With

          lien14 = Chr(34) & Sheets("feuil1").Range("A15") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B15") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (14)", Formula:= _
        "let" & Chr(13) & "" & Chr(11) & "    Source = Web.Page(Web.Contents(" & lien14 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (14)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (13)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__14"
        .Refresh BackgroundQuery:=False
    End With

          lien15 = Chr(34) & Sheets("feuil1").Range("A16") & Chr(34)
          nomonglet = Chr(34) & Sheets("feuil1").Range("B16") & Chr(34)
     ActiveWorkbook.Queries.Add Name:="Table 0 (15)", Formula:= _
        "let" & Chr(13) & "" & Chr(11) & "    Source = Web.Page(Web.Contents(" & lien15 & "))," & Chr(13) & "" & Chr(10) & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
            ActiveSheet.Name = nomonglet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (15)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (13)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0__15"
        .Refresh BackgroundQuery:=False
    End With

End Sub

Merci

Le 08/01/2023 à 16:36

Bonjour Jeromechant

1) Ton code peu amplement être optimisé je pense en faisant une simple boucle (à tester)

Sub Macro1()
  Dim Ind As Integer
  Dim Lien As String, NomOnglet As String
  For Ind = 1 To 16
    Lien = Chr(34) & Sheets("feuil1").Range("A" & 1 + Ind) & Chr(34)
    If Lien = "" Then Exit For
    NomOnglet = Chr(34) & Sheets("feuil1").Range("B" & 1 + Ind) & Chr(34)
    With ActiveWorkbook
      .Queries.Add Name:="Table " & Ind, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" & Lien & "))," & Chr(13) & "" & Chr(10) _
        & "    Data12 = Source{12}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Data12,{{""Actions"", type text}})" _
        & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
      .Worksheets.Add
      With ActiveSheet
        .Name = NomOnglet
        With .ListObjects.Add(SourceType:=0, Source:= _
          "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table " & Ind & """;Extended Properties=""""", _
          Destination:=Range("$A$1")).QueryTable
          .CommandType = xlCmdSql
          .CommandText = Array("SELECT * FROM [Table " & Ind & "]")
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .PreserveColumnInfo = True
          .ListObject.DisplayName = "Table_" & Ind
          .Refresh BackgroundQuery:=False
        End With
      End With
    End With
  Next Ind
End Sub

2) Sinon pour arrêter ton code, il suffit de tester le contenu

lien1 = Chr(34) & Sheets("feuil1").Range("A2") & Chr(34)
If Lien1 = "" then Exit Sub

A+

Le 08/01/2023 à 16:42

Bonjour,

Merci.

comment puis je faire pour l'optimiser en sachant que j'aimerais bien faire de plus de 15 lignes

Le 08/01/2023 à 16:44

Re,

Le temps de modifier, j'ai mis la boucle unique dans mon précédent post

A tester

Le 08/01/2023 à 17:12

C'est super la boucle.

Mais je ne sais pas comment faire pour l'arrette quan il y a une case vide dans la colone des B

Le 08/01/2023 à 17:30

Re,

Sérieux... tu le fais exprès

Même test mais avec la variable "NomOnglet", ça ne te parait pas logique !?

Le 08/01/2023 à 17:55

Je suis perdu,

Le 08/01/2023 à 18:11

Re,

Ne pas faire d'effort un dimanche, je n'en ferais pas non plus, désolé

Le 08/01/2023 à 18:25

Merci d m'avoir deja aider.

Pour la macro qui n'est pas en bloucle , j'ai compris.

Mais pour arrete la macro qui est bloucle je bloque car ce que je veux c'est que ca s'arrete proprement quand la colone en B à la cellule vide

Apl
I0002168
I0002230
I0002340
I0001725
I0003466
I0000998
I0003469
I0001014
Rechercher des sujets similaires à "arrete macro vide"