Good guide for Excel/Query/SQL

[Laphan]

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

 

[Patrick Molloy]

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

 

[Ray at]

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

 

[Laphan]

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

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

 

[Laphan]

Hi Guys

Any ideas??


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

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

 

[Jake Marx]

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 here:

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]

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


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 here:

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]

 

[Jake Marx]

Hi Laphan,

Answers inline....

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

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).Formula="=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]

 

[Laphan]

Jake

You're a star. Please have a good weekend, cos you've made mine!!!

Rgds

Laphan

Hi Laphan,

Answers inline....

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

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).Formula="=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]