PDA

View Full Version : Solved: Query a worksheet in excel



cssamerican
10-07-2005, 09:18 PM
I am kind of learning how to use VB through the help files included with Excel, so if my question sounds stupid that's why. Seems you can find anything in the help file except for the things that you would think would be there. Enough with the dribble let me explain my problem.
I have a spreadsheet that is just a bigger version of this:

LastName .. FirstName BudgetCode . Quantity1 Quantity2 Quantity3 Quantity4
Simpson ... Bart .... 1110-1101 .. 10 ...... 10 ...... 20 ...... 10
Simpson ... Bart .... 1110-1201 .. 10 ...... 20 ...... 20 ...... 20
Simpson ... Lisa .... 1110-1101 .. 0 ....... 5 ....... 5 ....... 0
Simpson ... Lisa .... 1110-1101 .. 5 ....... 5 ....... 5 ....... 5
Skywalker . Luke .... 1110-1101 .. 5 ....... 25 ...... 4 ....... 1
Skywalker . Luke .... 1110-1201 .. 50 .......25 ...... 41 ...... 2

What I want to do is query this sheet and create a recordset based on the BudgetCode so I get this result if I set the BudgetCode to 1110-1101.

LastName .. FirstName BudgetCode . Sum of Quantity
Simpson ... Bart .... 1110-1101 .. 50
Simpson ... Lisa .... 1110-1101 .. 30
Skywalker . Luke .... 1110-1201 .. 35

This is the code I have written so far:

Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & _
ThisWorkbook.FullName & "';Extended Properties=Excel 8.0;"
Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = "SELECT FirstName, LastName, BudgetCode FROM [Sheet1$] _
WHERE BudgetCode = '" & UserForm1.ComboBox1.Value & "'" & _
"GROUP BY FirstName, LastName, BudgetCode"
.Open , , 3, 3
Sheets(3).Cells.Clear
Sheets(3).[a1].CopyFromRecordset rs
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub


I have tried using SUM and GROUP BY in the SQL statement in several combinations; however, I keep getting errors so I am not sure where I am screwing that up. Also I am not even really sure I can do what I want with an SQL statement. If I can, I would like someone to show me the proper way to write it. If I have to write some VBA code to manipulate the record set a push in the right direction would be much appreciated. Thanks in advance for the help.

XL-Dennis
10-08-2005, 06:01 AM
Hi and welcome to VBAX :)

May I ask why You use ADO & SQL in the first place? It would be more closer to use the Pivottable to summarize the wanted information which also can be used in VBA.
Second, using ADO within the same workbook increase the memory in used due to a memory leak (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998).

As for the SQL Query use the syntax in the below example where You should add a reference to the Microsoft DAO x.x Library (x.x is the available version number).


Option Explicit
Sub DAO_Database_Approach()
Const stExtens As String = "Excel 8.0;HDR=Yes;IMEX=1"
Const stSQL As String = "SELECT LastName, FirstName, BudgetCode, " _
& "SUM(Quantity1+Quantity2+Quantity3) " _
& "AS Quantity FROM [Sheet1$] " _
& "GROUP BY LastName, FirstName, BudgetCode"
'Variables for DAO.
Dim DAO_ws As DAO.Workspace
Dim DAO_db As DAO.Database
Dim DAO_rs As DAO.Recordset
Dim strDb As String
'Variables for Excel.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnTarget As Range
Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets(2)
With wsTarget
Set rnTarget = .Range("A2")
End With
strDb = wbBook.FullName
'Instantiate the DAO objects.
Set DAO_ws = DBEngine.Workspaces(0)
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(stSQL, dbOpenForwardOnly)

'Write the Recordset to the target range.
rnTarget.CopyFromRecordset DAO_rs

'Close the instances.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close

'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing
End Sub


Kind regards,
Dennis

cssamerican
10-08-2005, 07:58 AM
May I ask why You use ADO & SQL in the first place?
Well, my first version of this program I used loops and arrays because that?s what I know. The problem was I took forever to run, and it took an act of congress to make any changes. Since it seemed I was always making changes the ADO/SQL method seemed to be really simple once I realized I could do it, so that is the direction I decided to take. I have also dabbled with Delphi in the past and used this type of method to connect to a Paradox database, so when I seen this type of syntax in the help I kind of understood what I was looking at. I guess when you don't have any books to explain what all the different methods of accessing data are you just grab one and go with it.


It would be more closer to use the Pivottable to summarize the wanted information which also can be used in VBA.
I am still not exactly sure what a Pivot Table is, so I never really thought of using one http://vbaexpress.com/forum/images/smilies/001.gif


Second, using ADO within the same workbook increase the memory in used due to a memory leak (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998).
Ouch, I didn?t know that, and that would definitely be a problem down the road. Thanks for the heads up on that.


As for the SQL Query use the syntax in the below example where You should add a reference to the Microsoft DAO x.x Library (x.x is the available version number).
I added the reference so no issues there.

Your SQL Statement seems to be missing
& "WHERE BudgetCode = '" & UserForm1.ComboBox1.Value & "'" _
Where would I insert that...This is what I tried in my ADO code and it doesn't work:
With rs
Set .ActiveConnection = cn
.Source = "SELECT LastName, FirstName, BudgetCode, " _
& "SUM(Quantity1+Quantity2+Quantity3) " _
& "AS Quantity FROM [Sheet1$] " _
& "WHERE BudgetCode = '" & UserForm1.ComboBox1.Value & "'" _
& "GROUP BY FirstName, LastName, BudgetCode"
.Open , , 3, 3
Sheets(3).Cells.ClearContents
Sheets(3).[a1].CopyFromRecordset rs
.Close
End With
cn.Close
I am getting the following error
Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

In the code you provided me I ran it as is and got the follwing error
Run-time error '3601':
Too few parameters. Expected 3.
and its highlighted on this line
Set DAO_rs = DAO_db.OpenRecordset(stSQL, dbOpenForwardOnly)

XL-Dennis
10-08-2005, 09:06 AM
Hi,

I believe You should leave a space after the "'" in the following part of the SQL expression:


& "WHERE BudgetCode ='" & UserForm1.ComboBox1.Value & "' " _




I am getting the following error
Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed


Because You use apostrophes where it should not be any in the connection string as the following example shows:


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"


Both SQL and ADO/DAO are very sensitive so it may be tricky to track down the errors.



In the code you provided me I ran it as is and got the follwing error
Run-time error '3601':
Too few parameters. Expected 3.
and its highlighted on this line
Set DAO_rs = DAO_db.OpenRecordset(stSQL, dbOpenForwardOnly)


Hm, have You checked that the SQL Expression is correct as the parameters are optional.

Kind regards,
Dennis

Cyberdude
10-08-2005, 10:39 AM
Seems you can find anything in the help file except for the things that you would think would be there.
I couldn't agree with you more!

cssamerican
10-08-2005, 11:08 AM
I am now using your code ( Thanks for that ); however I had to simplify the SQL so I could attempt trouble shoot the problem I am having with it.
This SQL statement works
Const stSQL As String = "SELECT LastName, FirstName, BudgetCode " _
& "FROM [Sheet1$] " _
& "WHERE BudgetCode = '1110-2755'"
This one doesn't
Const stSQL As String = "SELECT LastName, FirstName, BudgetCode, " _
& "SUM(Quantity1) " _
& "AS Quantity " _
& "FROM [Sheet1$] " _
& "WHERE BudgetCode = '1110-2755'"
It gives me the following error:
Run-time error '3122':
You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.
It seems obvious to me that something is wrong with my SUM statement, but I went over it and over it and I just can't see anything wrong with it. I doubled check the header columns in my worksheet, and I even just used Quantity1 in the Select statement successfully without the SUM function. So I am very confident nothing is preventing it from being able to recognize that columns header. Do you have any advice on how to rectify this problem?

XL-Dennis
10-08-2005, 12:04 PM
Hi,

All aggregating expressions in SQL (SUM, AVERAGE et al) require that every fieldname we want to retrieva data for is included in the GROUP BY.

So therefore LastName, FirstName, BudgetCode must be included in the GROUP BY part of the SQL query.

Hopefully this will solve the issue :)

Kind regards,
Dennis

cssamerican
10-08-2005, 04:20 PM
Thanks for all the help...just in case someone ever pulls this up this is the final version of the code and SQL Statement that worked the way I wanted it to.
Option Explicit
Sub DAO_Database_Approach()
Const stExtens As String = "Excel 8.0;HDR=Yes;IMEX=1"

'Variables for DAO.
Dim DAO_ws As DAO.Workspace
Dim DAO_db As DAO.Database
Dim DAO_rs As DAO.Recordset
Dim strDb As String
Dim strSQL As String
strSQL = "SELECT LastName, FirstName, BudgetCode, " _
& "SUM(Quantity1+Quantity2+Quantity3) " _
& "AS Quantity " _
& "FROM [Sheet1$] " _
& "WHERE BudgetCode = '" & UserForm1.ComboBox1.Value & "' " _
& "GROUP By LastName, FirstName, BudgetCode"

'Variables for Excel.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnTarget As Range
Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets("Reports")
With wsTarget
.Cells.ClearContents
Set rnTarget = .Range("A1")
End With
strDb = wbBook.FullName

'Instantiate the DAO objects.
Set DAO_ws = DBEngine.Workspaces(0)
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(strSQL, dbOpenForwardOnly)

'Write the Recordset to the target range.
rnTarget.CopyFromRecordset DAO_rs

'Close the instances.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close

'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing
End Sub

XL-Dennis
10-08-2005, 04:50 PM
You're welcome :)

Kind regards,
Dennis

gibbo1715
10-08-2005, 11:29 PM
I used a different approach to a similar problem by using the filter command to copy data across to another sheet, this is the fastest way i have found so far of doing this, all you need to do is change the column you want to search by and it will copy data to a new sheet with the header row included


'Set the column to filter (In This Case 1 or A)
'Change as required 1 = column A, 2 = Column b and so on
rng.AutoFilter Field:=1, Criteria1:=Choice



Hope this helps

Have a look at this kb entry

http://www.vbaexpress.com/kb/getarticle.php?kb_id=786&PHPSESSID=01478211d73bc2bfd4d1ffd3ebffc7ec

Gibbo

XL-Dennis
10-09-2005, 05:48 AM
Gibbo - http://www.ozgrid.com/forum/showthread.php?p=101007 ;)

Kind regards,
Dennis

gibbo1715
10-09-2005, 06:56 AM
Thanks Dennis,

gibbo