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]