Click here to Skip to main content
14,872,052 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
The VBA code below is for querying data from EXCEL data source,just like SQL
VB
PUBLIC dic As Object

Function P_query(spath, sFields, swhere, Optional wsName = "Sheet1")
'If Application.Calculation = xlCalculationAutomatic Then Application.Calculation = xlCalculationManual

  Dim rs As New ADODB.Recordset
  Dim sql$, itype$, val
  Dim arrResult
  Dim iscal As Boolean
  If iscal Then Exit Function Else iscal = True
  
  If dic Is Nothing Then Set dic = CreateObject("Scripting.Dictionary")
  P_query = Array("")
  If Len(sFields) = 0 Or sFields = "[]" Then Exit Function
  If Dir(spath, 16) = Empty Then MsgBox "数据源路径不存在": Exit Function
  If Len(swhere) > 0 Then swhere = " where " & swhere

  If Not dic.exists(spath) Then
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    With cnn
      If Application.Version = "11.0" Then
        .Provider = "microsoft.jet.oledb.4.0"
        .ConnectionString = "extended properties=""excel 8.0;HDR=YES;IMEX=1"";data source=" & spath
      Else
        .Provider = "microsoft.ACE.oledb.12.0"
        .ConnectionString = "extended properties=""excel 12.0;HDR=YES;IMEX=1"";data source=" & spath
      End If
      .Open
    End With
    dic.Add spath, cnn
  End If
  
    sql = "select " & sFields & " from [" & wsName & "$]" & swhere
    rs.CursorLocation = adUseClient
    On Error GoTo err1
    rs.Open sql, dic(spath), 1, 1
    If rs.RecordCount = 0 Then P_query = "#N/A": rs.Close: Set rs = Nothing: Exit Function
    If rs.EOF Then rs.MoveFirst
    If rs.Fields(0).Type = 5 Then itype = "Double" Else itype = "String"
    val = rs.GetRows()
    P_query = IIf(IsNull(val(0, 0)), "#N/A", Format(val(0, 0), "standard"))
    rs.Close
    Set rs = Nothing
  Exit Function
err1:
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    Set dic = Nothing
    Debug.Print "ERR" & "--" & Err.Description
    P_query = "#Value"
End Function


What I have tried:

I had called the function in the excel cells,weird!!!,Some worked,while others failed.
And I had also debug it ,weird!!!,it circulated so that it couldn't jump out the function, what was worse,the public variable dic also couldn't work well,Although the Variable-dic contained the key-spath, the code "Not dic.exists(spath)" came out to be FALSE
So,I asked master for help here,Tks anymore
Posted
Updated 10-Jul-17 2:23am
v2
Comments
Maciej Los 10-Jul-17 7:59am
   
I'd suggest to open Recodset in static mode. Follow the instruction to find out why: Recodset.Open method
Another tip: use SELECT TOP(1) FROM ... to get single record and then value = rs.Fiels(0).
Member 12173487 10-Jul-17 22:14pm
   
Tks any more,I correct the code as "rs.Open sql, dic(spath), 3, 1", But it can't work.

You have cells that work and others that don't ... work out what the differences between those cells are to determine what the problem is. It is not weird to call functions from Cells formulae.

Debugging is good - it is also not weird. "Couldn't jump out of the function" - then work out where it went (use F8 to step through). Take a note of which values are making it hang. Make sure you have a MoveNext in all paths through any loops.

The following line of code is probably wrong
VB
If Not dic.exists(spath) Then
Why do I think it is wrong ... because you then try to connect to the file in spath having just determined that the file does not exist.

dic.exists(spath) will return True if the file exists. So putting the Not in front of it will return False if the file exists.
   
Comments
Member 12173487 10-Jul-17 21:48pm
   
Not reallly,the code "dic.exists(spath)" is not for determining whether the file exists.It is a tag about whether the connection has been established
CHill60 11-Jul-17 9:55am
   
The point still applies. sPath is in the collection so putting a Not in front of it will return False.
Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
   
Comments
Member 12173487 10-Jul-17 21:54pm
   
I think you are right,It is dangerous because of the "SQL injection"...But actually,the code is only for internal use,and those who own the datasource
CHill60 11-Jul-17 10:37am
   
Internal or not, you should get into the habit of writing safe queries

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900