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?
This is the function that does the work (It fails on the RST.OPEN):
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 subThis 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
