Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
Hi All
I'm trying to get far better control when creating Excel/VBA reporting of SQL data. I don't want the user messing with query, so I use the Macros/VBA part to create the querying in the background. Only problem is that at present all I can do with this is just retrieve one 'chunk' of data I don't seem to have the flexibility like I can with ASP. Can anybody give me pointers on how I can get the same versatility in Excel. Thanks Laphan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
VBA and ASP are both VB deriavtives ( loosely) and IMHO
I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. However, when it comes to querying a database, VBA is extremely flexible. You can very easily create complex TSQL code on the fly as well as saving the code to cells. Also, ADO recordsets can themselves be used to filter their data and one can even "find" records that match certain criteria. If you described a little of what you're trying to do, then maybe we can help further. Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi All I'm trying to get far better control when creating Excel/VBA reporting of SQL data. I don't want the user messing with query, so I use the Macros/VBA part to create the querying in the background. Only problem is that at present all I can do with this is just retrieve one 'chunk' of data I don't seem to have the flexibility like I can with ASP. Can anybody give me pointers on how I can get the same versatility in Excel. Thanks Laphan . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
"Patrick Molloy" wrote in message ... VBA and ASP are both VB deriavtives ( loosely) and IMHO I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. Just to be picky, ASP is in no way a derivitate of VB, since VB is a language, and ASP is a technology, not a language. Okay, that's enough from me. Ray at home |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
Hi Guys
Thanks for the prompt replies. The background info is that the company I work for develops an accounts software package using SQL server as the RDBMS. We've (or more realistically I've) basically been backed into a corner to extract certain data out of this SQL database to report and display the data in a certain way for an IntraStat report. The reason for this is that our software currently isn't geared up for IntraStat, but because the user only uses limited parts of the system (ie, there are quite a number of unused fields that we can re-use) and a lot the requirements seem to be there in the db to start with, we are trying to 'fudge' a solution to meet a tight deadline. From my point of view, I want to have a far better grasp on Excel's VBA side so that I can produce the queries and reporting needed without it just being a case of using the less dynamic Ms Query Wizard. Like I said before, I know how to do ADO connections, commands and recordsets in ASP, but what is the syntax for Excel VBA? A quick example of what I can't fathom is, how do I query say table 1, put it's data in the worksheet starting from cell A1, put a blank row in after this data, query data from table 2 and put this data in after the blank line? How do I know where the 1st query's data lines end? Many thanks. Rgds Laphan Ray at <%=sLocation% <myfirstname at lane 34 . komm wrote in message ... "Patrick Molloy" wrote in message ... VBA and ASP are both VB deriavtives ( loosely) and IMHO I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. Just to be picky, ASP is in no way a derivitate of VB, since VB is a language, and ASP is a technology, not a language. Okay, that's enough from me. Ray at home |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
Hi Guys
Any ideas?? Laphan wrote in message ... Hi Guys Thanks for the prompt replies. The background info is that the company I work for develops an accounts software package using SQL server as the RDBMS. We've (or more realistically I've) basically been backed into a corner to extract certain data out of this SQL database to report and display the data in a certain way for an IntraStat report. The reason for this is that our software currently isn't geared up for IntraStat, but because the user only uses limited parts of the system (ie, there are quite a number of unused fields that we can re-use) and a lot the requirements seem to be there in the db to start with, we are trying to 'fudge' a solution to meet a tight deadline. From my point of view, I want to have a far better grasp on Excel's VBA side so that I can produce the queries and reporting needed without it just being a case of using the less dynamic Ms Query Wizard. Like I said before, I know how to do ADO connections, commands and recordsets in ASP, but what is the syntax for Excel VBA? A quick example of what I can't fathom is, how do I query say table 1, put it's data in the worksheet starting from cell A1, put a blank row in after this data, query data from table 2 and put this data in after the blank line? How do I know where the 1st query's data lines end? Many thanks. Rgds Laphan Ray at <%=sLocation% <myfirstname at lane 34 . komm wrote in message ... "Patrick Molloy" wrote in message ... VBA and ASP are both VB deriavtives ( loosely) and IMHO I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. Just to be picky, ASP is in no way a derivitate of VB, since VB is a language, and ASP is a technology, not a language. Okay, that's enough from me. Ray at home |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL
Hi Laphan,
Assuming you used vbscript on your ASP documents, then the Excel VBA version of connecting and getting data into a recordset would be almost identical to what you did in ASP. The only difference is that you would strongly type your variables (As ADODB.Recordset, etc). You would need to set a reference (via Tools | References in the VBE) to Microsoft ActiveX Data Objects version x.x in order for the project to compile. To put a recordset on your worksheet, you can use the CopyFromRecordset method of the Range object: Sheets("MyData").Range("A1").CopyFromRecordset rsMyRecordset To find the last row of data, you would do something like this: Dim lLastRow As Long With Sheets("MyData") lLastRow = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1).Row End With So your next recordset would go he Sheets("MyData").Cells(lLastRow+2, 1).CopyFromRecordset rsMy2ndRecordset Keep in mind that Excel worksheets can only handle 65,536 rows, so if your data sets are large, you may have to put each one on a separate worksheet. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Laphan wrote: Hi Guys Any ideas?? Laphan wrote in message ... Hi Guys Thanks for the prompt replies. The background info is that the company I work for develops an accounts software package using SQL server as the RDBMS. We've (or more realistically I've) basically been backed into a corner to extract certain data out of this SQL database to report and display the data in a certain way for an IntraStat report. The reason for this is that our software currently isn't geared up for IntraStat, but because the user only uses limited parts of the system (ie, there are quite a number of unused fields that we can re-use) and a lot the requirements seem to be there in the db to start with, we are trying to 'fudge' a solution to meet a tight deadline. From my point of view, I want to have a far better grasp on Excel's VBA side so that I can produce the queries and reporting needed without it just being a case of using the less dynamic Ms Query Wizard. Like I said before, I know how to do ADO connections, commands and recordsets in ASP, but what is the syntax for Excel VBA? A quick example of what I can't fathom is, how do I query say table 1, put it's data in the worksheet starting from cell A1, put a blank row in after this data, query data from table 2 and put this data in after the blank line? How do I know where the 1st query's data lines end? Many thanks. Rgds Laphan Ray at <%=sLocation% <myfirstname at lane 34 . komm wrote in message ... "Patrick Molloy" wrote in message ... VBA and ASP are both VB deriavtives ( loosely) and IMHO I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. Just to be picky, ASP is in no way a derivitate of VB, since VB is a language, and ASP is a technology, not a language. Okay, that's enough from me. Ray at home |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL - ping Jake
Jake
This is BRILLIANT stuff. Just what I'm looking for. I know I'm going to start to be a pain now, but can I ask you (or any others out there) the following: 1) Is there a guide anywhere on this strong type method? As far as I can fathom, the only bit that seems different is that you have to dim your vars with 'As ADODB Recordset, etc'. Is this really it? 2) Where does one learn all this Cells, Rows, End up malaki??? Is there any kind of no b*&*&t syntax listed anywhere? Putting the extra lines and recordsets is exactly what I want, but I need to know how to say do the following: a) Know which row it is to format the columns header of each recordset and to know how many columns are in each recordset to format. b) Sum up say a column of recordset data (less the column header) and put the total either directly underneath it or say at the very bottom of the report. How can one do this? c) The ultimate question - can this sort of thing be done on Excel 98/2001 for Mac?? I know the queries can be formatted on this platform, but I bet there isn't a lovely References item under Tools for the ADo reference - correct? Once I can get a firm grasp of the above, I'm set (promise!!). Thanks Laphan Jake Marx wrote in message ... Hi Laphan, Assuming you used vbscript on your ASP documents, then the Excel VBA version of connecting and getting data into a recordset would be almost identical to what you did in ASP. The only difference is that you would strongly type your variables (As ADODB.Recordset, etc). You would need to set a reference (via Tools | References in the VBE) to Microsoft ActiveX Data Objects version x.x in order for the project to compile. To put a recordset on your worksheet, you can use the CopyFromRecordset method of the Range object: Sheets("MyData").Range("A1").CopyFromRecordset rsMyRecordset To find the last row of data, you would do something like this: Dim lLastRow As Long With Sheets("MyData") lLastRow = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1).Row End With So your next recordset would go he Sheets("MyData").Cells(lLastRow+2, 1).CopyFromRecordset rsMy2ndRecordset Keep in mind that Excel worksheets can only handle 65,536 rows, so if your data sets are large, you may have to put each one on a separate worksheet. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Laphan wrote: Hi Guys Any ideas?? Laphan wrote in message ... Hi Guys Thanks for the prompt replies. The background info is that the company I work for develops an accounts software package using SQL server as the RDBMS. We've (or more realistically I've) basically been backed into a corner to extract certain data out of this SQL database to report and display the data in a certain way for an IntraStat report. The reason for this is that our software currently isn't geared up for IntraStat, but because the user only uses limited parts of the system (ie, there are quite a number of unused fields that we can re-use) and a lot the requirements seem to be there in the db to start with, we are trying to 'fudge' a solution to meet a tight deadline. From my point of view, I want to have a far better grasp on Excel's VBA side so that I can produce the queries and reporting needed without it just being a case of using the less dynamic Ms Query Wizard. Like I said before, I know how to do ADO connections, commands and recordsets in ASP, but what is the syntax for Excel VBA? A quick example of what I can't fathom is, how do I query say table 1, put it's data in the worksheet starting from cell A1, put a blank row in after this data, query data from table 2 and put this data in after the blank line? How do I know where the 1st query's data lines end? Many thanks. Rgds Laphan Ray at <%=sLocation% <myfirstname at lane 34 . komm wrote in message ... "Patrick Molloy" wrote in message ... VBA and ASP are both VB deriavtives ( loosely) and IMHO I'd say that VBA offers greater functionality than ASP. That of course depends on the application to a degree. Just to be picky, ASP is in no way a derivitate of VB, since VB is a language, and ASP is a technology, not a language. Okay, that's enough from me. Ray at home |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL - ping Jake
Hi Laphan,
Answers inline.... Laphan wrote: 1) Is there a guide anywhere on this strong type method? As far as I can fathom, the only bit that seems different is that you have to dim your vars with 'As ADODB Recordset, etc'. Is this really it? VBScript code should run as is under the VBA environment (assuming you don't have any references to ASP-related objects). But VBA (like VB) supports explicit typing of variables, so you should take advantage of it. String variables should be declared As String, Integers As Integer, and so on. For anything in the ADODB library, you should use ADODB.x, where x is the class corresponding to the object you wish to use. For a recordset, you would use As ADODB.Recordset, for example. Then, when you're creating instances of these objects, you can use Set <myvar = New ADODB.x instead of CreateObject or Server.CreateObject. 2) Where does one learn all this Cells, Rows, End up malaki??? Is there any kind of no b*&*&t syntax listed anywhere? There are several Excel-related sites that do a good job of introducing people to VBA in Excel. Here's a link to a good list of them: http://j-walk.com/ss/excel/links/ John's book (Power Programming), BTW, is considered one of the best for learning Excel VBA: http://j-walk.com/ss/books/bookxl19.htm Putting the extra lines and recordsets is exactly what I want, but I need to know how to say do the following: a) Know which row it is to format the columns header of each recordset and to know how many columns are in each recordset to format. You can format the entire row if you're just using Bold or Underline. If you want to use fills or borders, you'll have to know the number of columns. You can get the number of columns by checking the count of Fields in the recordset: nNumCols = rsMyRecordset.Fields.Count As far as referencing the header row, you can use something like this: Sheets("MyData").Rows(lLastRow+2) If you need the range of headers for fills or borders, you can use this: Dim rngHeaders As Range With Sheets("MyData") Set rngHeaders = .Range(.Cells(lLastRow+2, 1), .Cells(lLastRow+2, nNumCols)) End With b) Sum up say a column of recordset data (less the column header) and put the total either directly underneath it or say at the very bottom of the report. How can one do this? If you want to sum up column A, for example, you could to this: Dim sAddress As String With Sheets("MyData") sAddress = .Range(.Cells(lLastRow+2,1), ..Cells(lLastRow+2,1).End(xlDown)).Address .Cells(lLastRow+2,1).End(xlDown).Offset(1,0).Formu la="=SUM(" & sAddress & ")" End With c) The ultimate question - can this sort of thing be done on Excel 98/2001 for Mac?? I know the queries can be formatted on this platform, but I bet there isn't a lovely References item under Tools for the ADo reference - correct? I don't know - I haven't worked on a Mac in 10 years. :) I would think there is some way to set project references, though. NOTE: The code snippets above haven't been tested, so they may contain some syntax errors. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good guide for Excel/Query/SQL - ping Jake
Jake
You're a star. Please have a good weekend, cos you've made mine!!! Rgds Laphan Jake Marx wrote in message ... Hi Laphan, Answers inline.... Laphan wrote: 1) Is there a guide anywhere on this strong type method? As far as I can fathom, the only bit that seems different is that you have to dim your vars with 'As ADODB Recordset, etc'. Is this really it? VBScript code should run as is under the VBA environment (assuming you don't have any references to ASP-related objects). But VBA (like VB) supports explicit typing of variables, so you should take advantage of it. String variables should be declared As String, Integers As Integer, and so on. For anything in the ADODB library, you should use ADODB.x, where x is the class corresponding to the object you wish to use. For a recordset, you would use As ADODB.Recordset, for example. Then, when you're creating instances of these objects, you can use Set <myvar = New ADODB.x instead of CreateObject or Server.CreateObject. 2) Where does one learn all this Cells, Rows, End up malaki??? Is there any kind of no b*&*&t syntax listed anywhere? There are several Excel-related sites that do a good job of introducing people to VBA in Excel. Here's a link to a good list of them: http://j-walk.com/ss/excel/links/ John's book (Power Programming), BTW, is considered one of the best for learning Excel VBA: http://j-walk.com/ss/books/bookxl19.htm Putting the extra lines and recordsets is exactly what I want, but I need to know how to say do the following: a) Know which row it is to format the columns header of each recordset and to know how many columns are in each recordset to format. You can format the entire row if you're just using Bold or Underline. If you want to use fills or borders, you'll have to know the number of columns. You can get the number of columns by checking the count of Fields in the recordset: nNumCols = rsMyRecordset.Fields.Count As far as referencing the header row, you can use something like this: Sheets("MyData").Rows(lLastRow+2) If you need the range of headers for fills or borders, you can use this: Dim rngHeaders As Range With Sheets("MyData") Set rngHeaders = .Range(.Cells(lLastRow+2, 1), .Cells(lLastRow+2, nNumCols)) End With b) Sum up say a column of recordset data (less the column header) and put the total either directly underneath it or say at the very bottom of the report. How can one do this? If you want to sum up column A, for example, you could to this: Dim sAddress As String With Sheets("MyData") sAddress = .Range(.Cells(lLastRow+2,1), ..Cells(lLastRow+2,1).End(xlDown)).Address .Cells(lLastRow+2,1).End(xlDown).Offset(1,0).Formu la="=SUM(" & sAddress & ")" End With c) The ultimate question - can this sort of thing be done on Excel 98/2001 for Mac?? I know the queries can be formatted on this platform, but I bet there isn't a lovely References item under Tools for the ADo reference - correct? I don't know - I haven't worked on a Mac in 10 years. :) I would think there is some way to set project references, though. NOTE: The code snippets above haven't been tested, so they may contain some syntax errors. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
And in case I don't see you,good afternoon,good evening,and good n | Excel Discussion (Misc queries) | |||
Excel 2007 Guide | Excel Discussion (Misc queries) | |||
Any good resource to learn Excel Web Query ".iqy" Files? | Excel Discussion (Misc queries) | |||
Microsoft Query User's Guide | Excel Discussion (Misc queries) | |||
Good VBA reference guide | Excel Programming |