Get data from a range in a Closed Workbook - ADO ERROR

doggo

Board Regular
Joined
Jan 22, 2004
Messages
115
Hi,

I am using ADO to retreive a value from a range in a closed excel workbook.
The code worked about a month ago in the same workbook but now when I run it I get an error saying:

The Microsoft Jet database engine could not find the object 'Setting_Version'. Make sure the object exists and that you spell its name and the path name correctly.

I know that the name exists in the sheet and that it contains valid data. There is also only 1 range in the workbook with that name. I tested the code by creating a blank workbook with that range and the code below ran perfectly. Any ideas where I am going wrong?

Code:
Public Sub VersionControl

dim dblLocalVersion  as double
const strLocalFile as string = "c:\Temp\myfile.xls"
const strVersionRange as string = "Setting_Version"

     dblLocalVersion = GetVersionNumber(strLocalFile, strVersionRange)

End sub

This is the function that does the work (It fails on the RST.OPEN):

Code:
Public Function GetVersionNumber(p_strFile As String, p_strVersionRange) As Double

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

    On Error GoTo Err_Handler

    'Create Connection to Local Template
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & p_strFile & ";" & _
               "Extended Properties=""Excel 8.0; HDR=No;"""
           
    rst.Open "Select * from " & p_strVersionRange, cnn, adOpenStatic
           
    If rst.RecordCount > 0 Then
        GetVersionNumber = CDbl(rst.Fields(0))
    Else
        GetVersionNumber = 0
    End If
    
EndThis:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
    Exit Function
    
Err_Handler:
    Err.Clear
    GetVersionNumber = 0
    Resume EndThis
          
End Function
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

doggo

Board Regular
Joined
Jan 22, 2004
Messages
115
Thanks for the alternative....

but I really need to get this method working as I have a number of users with this code running. Anyone else?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,283
Messages
5,571,303
Members
412,380
Latest member
Angelagrace
Top