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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,051
Messages
5,835,137
Members
430,343
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top