I am using Excel 2003 on a Windows XP machine connected to a server. I am trying to use the code below to read data from a closed Excel spreadsheet from within a macro on an open Excel spreadsheet. Whenever it gets to the
line, it actually opens the Excel spreadsheet, instead of just connecting to it. I have also tried using both of the commented lines in the code, with the same result. Does anyone have any idea why this is happening?
Code:
adoConn.Open "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=True;DBQ=" & sCurrDir & scFName
Code:
Public Function read_table(ByVal sSearchVal As String) As String
Dim sCurrDir As String
Dim Conn As Connection
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim sTab As String
Dim sRange As String
sCurrDir = ThisWorkbook.Path
If InStr(sCurrDir, scDevmnt) > 0 Then
sCurrDir = scDevTbl
Else
sCurrDir = scProdTbl
End If
sTab = "User Area 5"
sRange = "A1:B6"
Set adoConn = New ADODB.Connection
adoConn.Provider = "MSDASQL"
adoConn.CursorLocation = adUseClient
adoConn.Open "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=True;DBQ=" & sCurrDir & scFName
' adoConn.Open "DRIVER=Microsoft Excel Driver (*.xls);DriverId=790;DefaultDir=" & sCurrDir & ";DBQ=" & scFName
' adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sCurrDir & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Set adoRS = adoConn.Execute("SELECT TRANSLATION FROM [" & sTab & "$" & sRange & "] WHERE NBU='" & sSearchVal & "'")
If adoRS.EOF Then
read_table = sSearchVal
Else
read_table = adoRS.Fields(0)
End If
adoRS.Close
adoConn.Close
Set adoRS = Nothing
Set adoConn = Nothing
End Function