Exemple de macro

Exemple de Macro qui permet directement depuis excel de supprimer les données puis de recharger des données réactualisées.

Tout les mots surlignés sont a modifier selon avec les informations de l'utilisateur.

Sub VIGILENS_<font inherit/inherit;;inherit;;#f1c40f>Nom</font><font inherit/inherit;;inherit;;#f1c40f>de</font><font inherit/inherit;;inherit;;#f1c40f>la</font><font inherit/inherit;;inherit;;#f1c40f>requète</font><font inherit/inherit;;inherit;;#f1c40f>()</font>
'
' Supprimer les données
Call SUPP_VIGILENS_<font inherit/inherit;;inherit;;#f1c40f>Nom</font><font inherit/inherit;;inherit;;#f1c40f>de</font><font inherit/inherit;;inherit;;#f1c40f>la</font><font inherit/inherit;;inherit;;#f1c40f>requète</font>
'
Dim DATEVIG As Variant
Dim CLIENTVIG As Variant

DATEVIG = “&DATE=” + Worksheets(“<font inherit/inherit;;#ffffff;;#e74c3c>SHEET1</font>”).Range(“$C$1”).Text
CLIENTVIG = “&CLIENT=” + Worksheets(“<font inherit/inherit;;#ffffff;;#e74c3c>SHEET1</font>”).Range(“$C$2”).Text

'MsgBox (DATEVIG) Paramètre à l'excution de la requète VIGILENS
'MsgBox (CLIENTVIG) Paramètre à l'excution de la requète VIGILENS

With Worksheets(“<font inherit/inherit;;#ffffff;;#16a085>SHEET DATA</font>”).QueryTables.Add(Connection:= _
“URL;http://Nom <font inherit/inherit;;#ffffff;;#3498db>du serveur</font>/vigilens/table.php?vglOutput=XLTable&vglCtrlName=<font inherit/inherit;;inherit;;#f1c40f>chemin</font><font inherit/inherit;;inherit;;#f1c40f>\</font><font inherit/inherit;;inherit;;#f1c40f>Nom</font><font inherit/inherit;;inherit;;#f1c40f>de</font><font inherit/inherit;;inherit;;#f1c40f>la</font><font inherit/inherit;;inherit;;#f1c40f>requète</font>&vglUserName=<font inherit/inherit;;#ffffff;;#3498db>Nomdel'utilisteurVIGILENS</font>” + DATEVIG + CLIENTVIG _
, Destination:=Sheets(“<font inherit/inherit;;#ffffff;;#16a085>SHEET DATA</font>”).Range(“$A$1”)) .Name = “<font inherit/inherit;;inherit;;#f1c40f>Nom</font><font inherit/inherit;;inherit;;#f1c40f>de</font><font inherit/inherit;;inherit;;#f1c40f>la</font><font inherit/inherit;;inherit;;#f1c40f>requète</font><font inherit/inherit;;inherit;;#f1c40f>Vigilens</font>”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Sub SUPP_VIGILENS_<font inherit/inherit;;inherit;;#f1c40f>Nom</font><font inherit/inherit;;inherit;;#f1c40f>de</font><font inherit/inherit;;inherit;;#f1c40f>la</font><font inherit/inherit;;inherit;;#f1c40f>requète</font><font inherit/inherit;;inherit;;#f1c40f>()</font>
'
'cette Macro supprime les données
'
On Error Resume Next
With Worksheets(“<font inherit/inherit;;#ffffff;;#16a085>SHEET DATA</font>”)
.QueryTable.Delete
.Columns(“A:XX”).ClearContents
End With
End Sub

Sub VIGILENS_Nom de la requ�te()
'
' Supprimer les donn�es
Call SUPP_VIGILENS_Nom de la requ�te
'
Dim DATEVIG As Variant
Dim CLIENTVIG As Variant
  
    DATEVIG = "&DATE=" + Worksheets("SHEET1").Range("$C$1").Text
    CLIENTVIG = "&CLIENT=" + Worksheets("SHEET1").Range("$C$2").Text
    
    'MsgBox (DATEVIG) Param�tre � l'excution de la requ�te VIGILENS
    'MsgBox (CLIENTVIG) Param�tre � l'excution de la requ�te VIGILENS

    With Worksheets("SHEET DATA").QueryTables.Add(Connection:= _
        "URL;http://Nom du serveur/vigilens/table.php?vglOutput=XLTable&vglCtrlName=chemin\Nom de la requ�te&vglUserName=Nom de l'utilisteur VIGILENS" + DATEVIG + CLIENTVIG _
        , Destination:=Sheets("SHEET DATA").Range("$A$1"))
        .Name = "Nom de la requ�te Vigilens"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub


Sub SUPP_VIGILENS_Nom de la requ�te()
'
'cette Macro supprime les donn�es
'
     On Error Resume Next
    With Worksheets("SHEET DATA")
        .QueryTable.Delete
        .Columns("A:XX").ClearContents
    End With
End Sub