PDA

View Full Version : Need Help with Querying an Excel Table in VBA. ADO?



brusk
11-01-2014, 08:37 AM
I have a workbook with dozens of tables that I'm currently using VBA to query and gather information and thought a SQL based query setup would be alot more efficient in gather the data I need for the worksheets. After searching I found ADO examples, is ADO the best/only solution? If so I could use a little guidance understanding some of this as it looks like ADO is used to query alot more than just excel so the examples I found differ quite a bit. I have Office 2010 Pro Plus. In the examples it looks like I need to add a reference to Microsoft ActiveX Data Objects Library. I would assume to pick the latest listed on my computer which is 6.1 but I have several 2.x versions listed. Also will this affect users that may still be running an older version of Excel.

I currently have this exampled plugged into a test sheet to see if it will at least run but fails at the rs.Open point so I figure something probably isn't correct on the Connection string info.

Public Function SQL() As Variant
Dim wb As Workbook
Dim cd As Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strRangeAddress As String
Dim strFile As String
Dim strCon As String
Dim strSQL As String

Set wb = ThisWorkbook
Set cd = wb.Sheets("ConfigurationData")

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon
'strRangeAddress = Mid(ActiveWorkbook.Names.Item("cnfTableSystems").RefersToLocal, 2)
strRangeAddress = cd.Name & cd.Range("cnfTableSystems").Address
strSQL = "SELECT * FROM [" & strRangeAddress & "]"

rs.Open strSQL, cn

Debug.Print rs.GetString
SQL = rs.GetString
End Function

snb
11-01-2014, 10:20 AM
Please give an example (in a file) which information you want to retrieve from a table.
Only when we know what you are after we can advise whether a different approach could be better.

Bob Phillips
11-01-2014, 12:23 PM
Post the workbook to be queried, makes life much easier.

p45cal
11-01-2014, 04:58 PM
…and don't forget to tell people where you've cross posted to:
http://www.mrexcel.com/forum/excel-questions/815463-need-help-querying-excel-table-visual-basic-applications-ado.html

brusk
11-01-2014, 07:57 PM
Here's a stripped down version without customer information, there a basic userform with a button to the SQL function.. Seriously nothing much on this jus trying to figure out if ADO is the right path for what I'm trying to accomplish. I'm use to SQL/Sybase in the past all this excel is a major step back in my opinion but the requirement was a single file offline based spreadsheet format. If ADO is the way to go I'm assuming there a basic misconfiguration in what I've previously posted.

snb
11-02-2014, 03:58 AM
What is the relationship between those 2 tables ??

What result are you after ???

What is your question ??

brusk
11-02-2014, 10:57 AM
In the end I would love to be able to have something like
select systems.Alias, Network.ServerType, Network.Host, Network.Hostname, Network.IPAddress from cnfTableSystems Systems, cnfTableQMNetwork Network Where Systems.AssetID = Network.AssetID
but right now I'm just trying to figure out the basics and return all the data from one table just to make sure I have the connection string etc setup properly. As I said I couldn't find very much info about ADO so I'm hoping thats the best method to query the Excel Tables and also hoping it truly does give the same usage as I would use SQL command with a database.

Kenneth Hobs
11-03-2014, 02:52 PM
There are several ways to do it. The first step is to get the connection string. http://www.connectionstrings.com/

Second or even first might be to set the ADO reference if you are using early binding as you did in your code.

Once you get those two steps done, a search of this forum or general search will find many ways to do it. I have not worked with tables like that so I am not which approach is best.

QueryTables can be used.

Like your example, something from this might help. Of course there are several like that here on this forum as well.
'romperstomper, http://www.excelforum.com/excel-programming/665066-send-results-of-sql-query-to-an-array.html

As a function:

'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23640071.html
Public Sub RunSQLQuery( _
ByVal SourceWorkbookPath As String, _
ByVal SQLCommand As String, _
ByRef Destination As Variant, _
Optional TablesHaveHeaders As Boolean = True, _
Optional WriteHeader As Boolean _
)


' Run SQL command against one or more tables in a workbook and write the
' resulting recordset to the range starting at the destination specified.
'
' Syntax
'
' RunSQLQuery(SourceWorkbookPath, SQLCommand, Destination, [WriteHeader])
'
' SourceWorkbookPath - The full path to the workbook in which the source tables
' reside. The workbook must have been previously saved to disk (although the
' most current version does not need to be saved to get the most recent
' data). To get the full path for an open workbook using the workbook object
' use:
' SourceWorkbookPath.Path & "\" & SourceWorkbookPath.Name
' The source workbook can be open or closed.
'
' SQLCommand - Any valid SQL command. Reference tables either by cell
' reference:
' SELECT * FROM [Sheet1$A1:C100]
' or by range name:
' SELECT * FROM [Table1]
' Field names are determined by the values in the first row of each table. To
' reference a field name use square brackets around the name:
' SELECT T1.[FieldName] FROM [Table1] AS T1
'
' Destination - Any valid range or a variant variable. If a range is passed
' then the resulting recordset is copied to the cells starting at the top
' left cell in Destination. As many cells are used as are needed. If an empty
' array is passed (UBound = -1) then the query result is returned as an
' array. Otherwise the resulting recordset is returned in Destination.
'
' TablesHaveHeaders - Pass True if the source tables have a header row. Pass
' False if not. Optional. If omitted then True is assumed. If the source
' tables do not have headers then the columns are named "F1", "F2", etc.
'
' WriteHeader - Pass True to write a header row, False to not write the
' headers. Optional. If omitted then False is assumed.


Dim RecordSet As Object ' ADODB.RecordSet
Dim ConnectionString As String
Dim Column As Long
Dim Row As Long
Dim ReturnArray As Boolean
Dim RecordCount As Long

Set RecordSet = CreateObject("ADODB.RecordSet") ' New ADODB.RecordSet

If IsArray(Destination) Then
If UBound(Destination) = -1 Then ReturnArray = True
End If

' Open the ADODB connection and run the query
ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceWorkbookPath & ";" & _
"Extended Properties='Excel 8.0;HDR=" & IIf(TablesHaveHeaders, "Yes", "No") & "'"
RecordSet.Open SQLCommand, ConnectionString, 2, 1, 1 ' adOpenDynamic, adLockReadOnly, adCmdText

' Handle the results according to what was passed in Destination
If TypeName(Destination) = "Range" Then
' Copy recordset to range
Application.ScreenUpdating = False
If WriteHeader Then
For Column = 1 To RecordSet.Fields.Count
Destination.Cells(1, Column).Value = RecordSet.Fields(Column - 1).Name
Next Column
Destination.Cells(1, 1).Resize(1, RecordSet.Fields.Count).Font.Bold = True
Set Destination = Destination.Offset(1)
End If
Destination.CopyFromRecordset RecordSet
Application.ScreenUpdating = True
RecordSet.Close
ElseIf ReturnArray Then
' Return an array
RecordSet.MoveFirst
Do While Not RecordSet.EOF
RecordCount = RecordCount + 1
RecordSet.MoveNext
Loop
ReDim Destination(1 To RecordCount + IIf(WriteHeader, 1, 0), 1 To RecordSet.Fields.Count)
Row = 1
If WriteHeader Then
For Column = 1 To RecordSet.Fields.Count
Destination(Row, Column) = RecordSet.Fields(Column - 1).Name
Next Column
Row = Row + 1
End If
RecordSet.MoveFirst
Do While Not RecordSet.EOF
For Column = 1 To RecordSet.Fields.Count
Destination(Row, Column) = RecordSet.Fields(Column - 1).Value
Next Column
Row = Row + 1
RecordSet.MoveNext
Loop
RecordSet.Close
Else
' Return recordset
Set Destination = RecordSet
End If


End Sub




Sometimes something simple works:

'northwolves, http://www.mrexcel.com/forum/showthread.php?t=371840
Sub Test()
Dim sql As String
With CreateObject("ADODB.connection")
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;imex=1;hdr=no;';Data Source=" & ThisWorkbook.FullName
sql = "SELECT f1,f3,f5,f8 FROM [sheet1$a2:h65536] where f1 in (select f1 from [sheet2$a2:a65536])"
Sheet3.[a1].CopyFromRecordset .Execute(sql)
.Close
End With
End Sub

Aflatoon
11-04-2014, 03:43 AM
You need to append "$" to the sheet name and remove the "$" from the range address:

strRangeAddress = cd.Name & "$" & cd.Range("cnfTableSystems").Address(0, 0)

Also note that you can't use GetString twice in succession without using rs.MoveFirst in between.

brusk
11-08-2014, 08:58 AM
Hey guys I really appreciate the help. I tried both of those methods and the longer version seems to be less error prone to what I've thrown at it. Had to add a piece for Null value handling but for the basics its working good. Now I'm having problems trying to pull data across two tables. Here's the code where I'm passing the info to the procedure.


Dim sTable1 As String
Dim sTable2 As String
Dim arResults() As String
ReDim arResults(-1 To -1)

sTable1 = cd.Name & "$" & cd.ListObjects("cnfTableUIPNetwork").Range.Address(Fale, False)
sTable2 = cd.Name & "$" & cd.ListObjects("cnfTableSystems").Range.Address(Fale, False)

Call RunSQLQuery(ThisWorkbook.Name, "Select NW.[AssetID], S.[Alias], NW.[Role] from [" & sTable1 & "] As NW, [" & sTable2 & "] As S Where NW.[AssetID] = S.[AssetID]", arResults, True, False)

Heres what the SQL Portion looks once it gets to the procedure.

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] As NW, [ConfigurationData$B20:F27] As S Where NW.[AssetID] = S.[AssetID]"

I'm guessing this is just a basic syntax in the SQL command, maybe with how ADO handles the where clause between the tables. If you need another workbook thrown together let me know.

Thanks,
Bruce

Aflatoon
11-10-2014, 12:15 AM
You might at least say what the problem is... ;)
I think you should remove the 'As' from the table aliasing.

brusk
11-10-2014, 08:55 AM
You might at least say what the problem is... ;)
I think you should remove the 'As' from the table aliasing.

The problem is I'm new to ADO and it's not liking the SQL query when I try to add a 2nd table. No As? The examples I've seen had that in there. The error I get with or without the AS is 'Cannot Update. Database or Object is Read-Only'

Aflatoon
11-10-2014, 09:09 AM
Please try:

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

brusk
11-10-2014, 10:41 AM
Please try:

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

Same error. Here's the SQL after the conversion to make sure it matches.

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

brusk
11-10-2014, 10:54 AM
It looks like now for some reason I cant even do a basic 'Select * from' on a single table without getting the 'Cannot Update. Database or Object is Read-Only' error. It's failing at the following line trying to Open the RecordSet.


RecordSet.Open SQLCommand, ConnectionString, 2, 1, 1 ' adOpenDynamic, adLockReadOnly, adCmdText

brusk
11-10-2014, 11:13 AM
Looks like closing and reopening has fixed that problem. Somehow it was locking the tables. So after running it again and getting results from a single table I get a 'Type Mismatch in expression'.
After some trial and error making sure I can properly pull the fields I'm trying to get properly it looks like the part it doesn't like is the WHERE selection. If I take that out it runs and of course returns inproper data but with no errors.

brusk
11-10-2014, 11:51 AM
Ok I think I've found the problem. I need to go back through all my code it looks like some of the AssetID's are getting written as a number and some are writing then as text. I put together two test tables with only text and was able to query across those with no issues just as I should. Thanks everybody for the help this is going to make things alot easier once I fix these few issues.