Comment appeler une fonction?

Bonjour à tous,

J'ai été donné une fonction pour extraire des données via un site. Mais je ne sais pas comment appeler cette fonction. Je voulais ouvrir le fichier téléchargé, recopier des données dans ma feuille "Data" puis fermer le fichier.

Merci d'avance.

Sub gop() 
    Dim oWsBDR As Excel.Worksheet 

    On Error Resume Next 

    Set oWsBDR = WbBDR.Worksheets(CS_SHEET) 'Erreur : subscript out of range

    Worksheets("downloadFile").Cells.Copy 
    Workbooks("Task2").Sheets("Data").Paste 
    Application.CutCopyMode = False 
    Workbooks("Task2").Worksheets("Data") = "OPERATING GROUP MNEMONIC" 
    With Worksheets("Data").Cells 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlCenter 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
        .ColumnWidth = 30 
    End With 
    Set oWsBDR = Nothing 
    Application.DisplayAlerts = False 
    Windows("downloadFile.ln").Activate 
    ActiveWindow.Close 
End Sub 
Public Function WbBDR() As Excel.Workbook 
    Dim bOk As Boolean 
    Dim m_oME As Excel.Workbook 
    Dim m_oBDR As Excel.Workbook 

    If m_oBDR Is Nothing Then 
        bOk = False 
        Err.Clear 
        On Error Resume Next 
        Do Until bOk 
            Workbooks.OpenText Filename:="[url]https://.../downloadFile.ln?fileName=...[/url]", _ 
            Origin:=xlMSDOS, _ 
            StartRow:=1, _ 
            DataType:=xlDelimited, _ 
            TextQualifier:=xlDoubleQuote, _ 
            ConsecutiveDelimiter:=False, _ 
            Tab:=False, _ 
            Semicolon:=True, _ 
            Comma:=False, _ 
            Space:=False, _ 
            Other:=False, _ 
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ 
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _ 
            Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _ 
            Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _ 
            Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _ 
            Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _ 
            Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), _ 
            Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _ 
            Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), _ 
            Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _ 
            Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1)), _ 
            TrailingMinusNumbers:=True 
            If Err.Number = 0 Then bOk = True 
            Err.Clear 
        Loop 
        On Error GoTo 0 
        Set m_oBDR = Application.Workbooks("downloadFile.ln") 
    End If 
    Set WbBDR = m_oBDR 
End Function 

Bonsoir,

Tu pourrais par exemple l'appeler "ExtractionDonnees"

D'autres propositions ?

Bonne soirée

Bouben

Haha si seulement c'est la solution...

J'ai donc modifié le code, il donne une erreur : paste method of worksheet class failed.

Sauriez-vous pourquoi elle s'est produite?

Sub gop()

    Dim oWsBDR As Excel.Workbook

    'On Error Resume Next

    Set oWsBDR = WbBDR()

    If oWsBDR Is Nothing Then

        MsgBox "Le fichier n'a pas été extrait."

    Else

        'Set Workbooks("Task2").Worksheets("Data") = oWsBDR.Sheets("downloadFile")

        oWsBDR.Worksheets("downloadFile").Cells.Copy

        'Windows("Task2.xlsm").Cells(1, 1).Paste

        Windows("Task2.xlsm").Cells(1, 1).PasteSpecial xlPasteValues

        Application.CutCopyMode = False

        With Worksheets("Data").Cells

            .HorizontalAlignment = xlCenter

            .VerticalAlignment = xlCenter

            .WrapText = False

            .Orientation = 0

            .AddIndent = False

            .IndentLevel = 0

            .ShrinkToFit = False

            .ReadingOrder = xlContext

            .MergeCells = False

            .ColumnWidth = 30

        End With

        Set oWsBDR = Nothing

    End If

    Application.DisplayAlerts = False

    Windows("downloadFile.ln").Close

End Sub

Public Function WbBDR() As Excel.Workbook

    Dim bOk As Boolean

    Dim m_oME As Excel.Workbook

    Dim m_oBDR As Excel.Workbook

    If m_oBDR Is Nothing Then

        bOk = False

        Err.Clear

        On Error Resume Next

        Do Until bOk

            Workbooks.OpenText Filename:="https://.../downloadFile.ln?fileName=bdr_ast_gop", _

                Origin:=xlMSDOS, _

                StartRow:=1, _

                DataType:=xlDelimited, _

                TextQualifier:=xlDoubleQuote, _

                ConsecutiveDelimiter:=False, _

                Tab:=False, _

                Semicolon:=True, _

                Comma:=False, _

                Space:=False, _

                Other:=False, _

                FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _

                     Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _

                     Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _

                     Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _

                     Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _

                     Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _

                     Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), _

                     Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _

                     Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), _

                     Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _

                     Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1)), _

                     TrailingMinusNumbers:=True

            If Err.Number = 0 Then bOk = True

            Err.Clear

        Loop

        On Error GoTo 0

        Set m_oBDR = Application.Workbooks("downloadFile.ln")

    End If

    Set WbBDR = m_oBDR

End Function
Rechercher des sujets similaires à "comment appeler fonction"