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

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


.

"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

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

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

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