Code to call Excel spreadsheet in a function

[mastro78]

I know how to call/set CurrentDb and open the table I'm working with. Just curious as to how to do the same thing with an Excel worksheet and the associated cell data. Thanks.

 

[dinger2121]

Try using get object -

dim objXL as object
set objXL = getobject("","Excel.application")

 

[SkipVought]

Hi,

Example...

Function GetNbrRes(sResource As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sPrevCC As String, BCC As Boolean
    
    sPath = "D:\My Documents\_Databases\_Excel"
    sDB = "ResourceData"
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT ResCC "
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`ResourceData$` A "
    sSQL = sSQL & "WHERE Left(ResCC,1)='5' "
    sSQL = sSQL & "  AND Resource Like '%" & sResource & "%' "
    sSQL = sSQL & "Order By ResCC "
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
        GetNbrRes = rst(0)
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[mastro78]

I'm trying to fit this into my current OnOpen event where I'm gathering the Excel file info (name,date modified). Thanks Skip.