none
Recuperer valeur de cellule Excel dans VBA 2010

Toutes les réponses

  • Non, tu dois ouvrir le fichier pour prendre les valeurs.  Tu peux l'automatiser et le cacher des utilisateurs, mais il faut utilizer Exce d'une manière ou d'une autre.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    vendredi 1 juin 2018 11:20
  • Voici un exemple de fonction que tu peux te server comme point de départ pour récupérer une valeur d'une cellule Excel

    Function Excel_GetRangeVal(ByVal sFile As String, _
                               ByVal sSht As String, _
                               ByVal sRangeAdd As String) As Variant
        Dim oExcel                As Object
        Dim oExcelWrkBk           As Object
        Dim oExcelWrSht           As Object
        Dim bExcelOpened          As Boolean
    
        'Start Excel
        On Error Resume Next
        Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    
        If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
            Err.Clear
            Set oExcel = CreateObject("Excel.Application")
        Else    'Excel was already running
            bExcelOpened = True
        End If
        On Error GoTo Error_Handler
    
        oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
        oExcel.ScreenUpdating = False
    
        Set oExcelWrkBk = oExcel.Workbooks.Open(sFile)    'Open the Workbook
        Set oExcelWrSht = oExcelWrkBk.Sheets(sSht)    'Get the right WorkSheet to work with
        Excel_GetRangeVal = oExcelWrSht.Range(sRangeAdd).Value    'Get the Range value we are interested in
    
        oExcelWrkBk.Close False 'Close the WorkBook without saving now that we're done.
    
        If bExcelOpened = False Then 'Close excel if is wasn't originally running
            oExcel.Quit
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        oExcel.ScreenUpdating = True
        oExcel.Visible = True   'Make excel visible to the user
        Set oExcelWrSht = Nothing
        Set oExcelWrkBk = Nothing
        Set oExcel = Nothing
        Exit Function
    
    Error_Handler:
        '9      -> can't find the worksheet
        '1004   -> can't find the file
        If Err.Number = 9 Then
            MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: Export2XLS" & vbCrLf & _
                   "Error Description: Unable to locate the specified WorkSheet '" & sSht & "'" _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
            GoTo Error_Handler_Exit
        Else
            MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: Export2XLS" & vbCrLf & _
                   "Error Description: " & Err.Description _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
            Resume Error_Handler_Exit
        End If
    End Function


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    vendredi 1 juin 2018 13:07