Hi All

I'm sorry, but doing more involved Excel to data source querying in the Mac
version seems to have a large blank spot in the Internet community and I'm
struggling.

Before I starting looking at doing work again on the Mac, somebody informed
me about the Reference part of the PC version and allowed me to add the ADO
library that I know and love.

Developing multiple queries using the minimum overheads/connections and
knowing that I could gracefully close the objects and connections was an
absolute dream for me - not to mention the better control on where to put
these results once I'd got them - but the Mac just doesn't seem to have that
control and its tearing my hair out!!!

The steps I wish to take are as follows:

1) User enters a customer code into say cell A1 of worksheet 1.

2) User enters a start date in say cell B1 of worksheet 1.

3) User enters an end date in say cell C1 of worksheet 1.

4) User clicks a button on worksheet 1 to initiate a coded query.

5) The 1st query opens up a connection to an SQL database and then finds all
the invoices for the customer code entered.

6) In worksheet 1 starting from say cell A3, I 'post' the result of the 1st
query.

7) The coding then moves on to query all the sales orders for this customer
code between a date range using the same connection that was created in
point 5.

8) In worksheet 2 starting from say cell A3, I 'post' the result of the 2nd
query.

9) I do steps 7 and 8 again for 4 more queries with each query going into a
new worksheet and all using the same connection.

10) Once these queries have been completed, I then close the connection and
objects gracefully.

How can this be done via the Excel VBA version on a Mac (ie, 98 or 2001)??

Just as a side point, this querying will be done from a remote Mac
connecting to the PC/SQL Server via broadband, would it be more efficient to
run each query manually rather than the user having to wait for all 6
queries to complete? Is there anyway that Excel could let them know how far
the querying has gone?

Your assistance would be most appreciated.

Rgds

Laphan

Hi Laphan,

I'll intersperse my comments in your text below.

-Jim Gordon
MacMVP

----------
In article , "Laphan" wrote:



Hi All

I'm sorry, but doing more involved Excel to data source querying in the Mac
version seems to have a large blank spot in the Internet community and I'm
struggling.

Before I starting looking at doing work again on the Mac, somebody informed
me about the Reference part of the PC version and allowed me to add the ADO
library that I know and love.

There's no Active-X on the Mac. That's a long story in and of itself.

Developing multiple queries using the minimum overheads/connections and
knowing that I could gracefully close the objects and connections was an
absolute dream for me - not to mention the better control on where to put
these results once I'd got them - but the Mac just doesn't seem to have that
control and its tearing my hair out!!!

The steps I wish to take are as follows:

1) User enters a customer code into say cell A1 of worksheet 1.

2) User enters a start date in say cell B1 of worksheet 1.

3) User enters an end date in say cell C1 of worksheet 1.

You can capture the values of cells and put them into variables. Here's a
very simple example:

Sub GetACellValue()
Dim txtVariable As String
Let txtVariable = Range("A1")
MsgBox txtVariable
End Sub


4) User clicks a button on worksheet 1 to initiate a coded query.

Just about any object can be used to activate VB. Control-click on a button
to specify what subroutine to run. Same as Windows.

5) The 1st query opens up a connection to an SQL database and then finds all
the invoices for the customer code entered.

You will need to open a connection. Here's an example from Excel's help
file:
This example supplies new ODBC connection information for query table one.
Worksheets(1).QueryTables(1) _
.Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=N UyHwYQI;"

Then you execute the query. You can record examples in Excel 98.

Here's an example of a query (not a real one)
With Selection.QueryTable
.Connection = "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
.Sql = Array("SELECT* & vbLf & "FROM `CUSTOMER.DBF` CUSTOMER" & vbLf
& "WHERE (CUSTOMER.CITY='London')" )
End With
Substitute your variable(s) in the query.

If you record a query from scratch you'll see how to bind the query result
to a specific cell or range of cells.

You might also check out how to run a Parameter query (the user prompt is
built into the query).

The Excel VB help files have lots of examples on this stuff.

-Jim


6) In worksheet 1 starting from say cell A3, I 'post' the result of the 1st
query.

7) The coding then moves on to query all the sales orders for this customer
code between a date range using the same connection that was created in
point 5.

8) In worksheet 2 starting from say cell A3, I 'post' the result of the 2nd
query.

9) I do steps 7 and 8 again for 4 more queries with each query going into a
new worksheet and all using the same connection.

10) Once these queries have been completed, I then close the connection and
objects gracefully.

How can this be done via the Excel VBA version on a Mac (ie, 98 or 2001)??

Just as a side point, this querying will be done from a remote Mac
connecting to the PC/SQL Server via broadband, would it be more efficient to
run each query manually rather than the user having to wait for all 6
queries to complete? Is there anyway that Excel could let them know how far
the querying has gone?

Your assistance would be most appreciated.

Rgds

Laphan

Many thanks for the info Jim, but can you answer the following:

1) Where does the QueryTable come in? Do I have to pre-create this before I
perform the query or is it just like a temp table/recordset to hold the
result before I put it into something?

2) In your examples you should me how to open a connection, but then the
QueryTable creates the connection again - has this created 2 connections?

The reason for me being stingy on the old connection front, apart from a
resources point of view, is that our database only lets a set number of
users connect to it, because that is our bread and butter. If the 1 Excel
query hogs 3 user connections just to get different query data sets then the
user is going to be pretty miffed if they have a 5 user system and 3 have
been taken up by 1 user.

3) How do I gracefully disconnect these connections? Once the user grabs
the necessary data they could be looking at it and working on it in standard
Excel for a quite a few hours. If they have to quit Excel to close this
connections then this is going to be impractical and costly on the old
broadband.

4) IYHO, do you think it would be better to query each worksheet
individually and manually rather than do the whole process manually? 99% of
the queries would be huge queries, probably 25 rows a piece, but I'm just
thinking of the delay with the broadband.

Thanks

Laphan


Jim Gordon MVP wrote in message
...
Hi Laphan,

I'll intersperse my comments in your text below.

-Jim Gordon
MacMVP

----------
In article , "Laphan" wrote:



Hi All

I'm sorry, but doing more involved Excel to data source querying in the
Mac
version seems to have a large blank spot in the Internet community and I'm
struggling.

Before I starting looking at doing work again on the Mac, somebody
informed
me about the Reference part of the PC version and allowed me to add the
ADO
library that I know and love.

There's no Active-X on the Mac. That's a long story in and of itself.

Developing multiple queries using the minimum overheads/connections and
knowing that I could gracefully close the objects and connections was an
absolute dream for me - not to mention the better control on where to put
these results once I'd got them - but the Mac just doesn't seem to have
that
control and its tearing my hair out!!!

The steps I wish to take are as follows:

1) User enters a customer code into say cell A1 of worksheet 1.

2) User enters a start date in say cell B1 of worksheet 1.

3) User enters an end date in say cell C1 of worksheet 1.

You can capture the values of cells and put them into variables. Here's a
very simple example:

Sub GetACellValue()
Dim txtVariable As String
Let txtVariable = Range("A1")
MsgBox txtVariable
End Sub


4) User clicks a button on worksheet 1 to initiate a coded query.

Just about any object can be used to activate VB. Control-click on a button
to specify what subroutine to run. Same as Windows.

5) The 1st query opens up a connection to an SQL database and then finds
all
the invoices for the customer code entered.

You will need to open a connection. Here's an example from Excel's help
file:
This example supplies new ODBC connection information for query table one.
Worksheets(1).QueryTables(1) _
.Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=N UyHwYQI;"

Then you execute the query. You can record examples in Excel 98.

Here's an example of a query (not a real one)
With Selection.QueryTable
.Connection = "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
.Sql = Array("SELECT* & vbLf & "FROM `CUSTOMER.DBF` CUSTOMER" & vbLf
& "WHERE (CUSTOMER.CITY='London')" )
End With
Substitute your variable(s) in the query.

If you record a query from scratch you'll see how to bind the query result
to a specific cell or range of cells.

You might also check out how to run a Parameter query (the user prompt is
built into the query).

The Excel VB help files have lots of examples on this stuff.

-Jim


6) In worksheet 1 starting from say cell A3, I 'post' the result of the
1st
query.

7) The coding then moves on to query all the sales orders for this
customer
code between a date range using the same connection that was created in
point 5.

8) In worksheet 2 starting from say cell A3, I 'post' the result of the
2nd
query.

9) I do steps 7 and 8 again for 4 more queries with each query going into
a
new worksheet and all using the same connection.

10) Once these queries have been completed, I then close the connection
and
objects gracefully.

How can this be done via the Excel VBA version on a Mac (ie, 98 or 2001)??

Just as a side point, this querying will be done from a remote Mac
connecting to the PC/SQL Server via broadband, would it be more efficient
to
run each query manually rather than the user having to wait for all 6
queries to complete? Is there anyway that Excel could let them know how
far
the querying has gone?

Your assistance would be most appreciated.

Rgds

Laphan

Hi Laphan,

I'll intersperce again..

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm

----------
In article , "Laphan" wrote:

Many thanks for the info Jim, but can you answer the following:

1) Where does the QueryTable come in? Do I have to pre-create this before I
perform the query or is it just like a temp table/recordset to hold the
result before I put it into something?

This information is in Excel's help file. I'll copy it from the help file
and paste that he

+++
QueryTable
Represents a worksheet table built from data returned from an external data
source, such as an SQL server or a Microsoft Access database. The QueryTable
object is a member of the QueryTables collection.
Using the QueryTable Object
Use QueryTables(index), where index is the index number of the query table,
to return a single QueryTable object. The following example sets query table
one so that formulas to the right of it are automatically updated whenever
it's refreshed.
Sheets("sheet1").QueryTables(1).FillAdjacentFormul as = True
+++

In other words, a QueryTable is the range on the Excel worksheet that will
contain the result set received from Microsoft Query. Excel will create this
for you.

2) In your examples you should me how to open a connection, but then the
QueryTable creates the connection again - has this created 2 connections?

Those were intended to be two separate examples. You programmatically open
and close connections to the source data. Stick with one channel being open
at a time.

The reason for me being stingy on the old connection front, apart from a
resources point of view, is that our database only lets a set number of
users connect to it, because that is our bread and butter. If the 1 Excel
query hogs 3 user connections just to get different query data sets then the
user is going to be pretty miffed if they have a 5 user system and 3 have
been taken up by 1 user.

I recommend that you open only 1 connection at a time. The help files in MS
Query and Excel offer examples on how to manage the connections.

3) How do I gracefully disconnect these connections? Once the user grabs
the necessary data they could be looking at it and working on it in standard
Excel for a quite a few hours. If they have to quit Excel to close this
connections then this is going to be impractical and costly on the old
broadband.

This is explained in Excel's help files. There are examples. If you record a
session you will see the code.

4) IYHO, do you think it would be better to query each worksheet
individually and manually rather than do the whole process manually? 99% of
the queries would be huge queries, probably 25 rows a piece, but I'm just
thinking of the delay with the broadband.

25 rows will be almost instantaneous. You can turn screen updating off, run
the queries, then turn it back on. Excel can receive up to 65,535 rows of
data per worksheet with no restriction (except for available memory) on how
many worksheets you can have within a workbook. You can put more than 1
querytable on a worksheet if there's room.

Thanks

Laphan


Jim Gordon MVP wrote in message
...
Hi Laphan,

I'll intersperse my comments in your text below.

-Jim Gordon
MacMVP

----------
In article , "Laphan" wrote:



Hi All

I'm sorry, but doing more involved Excel to data source querying in the
Mac
version seems to have a large blank spot in the Internet community and I'm
struggling.

Before I starting looking at doing work again on the Mac, somebody
informed
me about the Reference part of the PC version and allowed me to add the
ADO
library that I know and love.

There's no Active-X on the Mac. That's a long story in and of itself.

Developing multiple queries using the minimum overheads/connections and
knowing that I could gracefully close the objects and connections was an
absolute dream for me - not to mention the better control on where to put
these results once I'd got them - but the Mac just doesn't seem to have
that
control and its tearing my hair out!!!

The steps I wish to take are as follows:

1) User enters a customer code into say cell A1 of worksheet 1.

2) User enters a start date in say cell B1 of worksheet 1.

3) User enters an end date in say cell C1 of worksheet 1.

You can capture the values of cells and put them into variables. Here's a
very simple example:

Sub GetACellValue()
Dim txtVariable As String
Let txtVariable = Range("A1")
MsgBox txtVariable
End Sub


4) User clicks a button on worksheet 1 to initiate a coded query.

Just about any object can be used to activate VB. Control-click on a button
to specify what subroutine to run. Same as Windows.

5) The 1st query opens up a connection to an SQL database and then finds
all
the invoices for the customer code entered.

You will need to open a connection. Here's an example from Excel's help
file:
This example supplies new ODBC connection information for query table one.
Worksheets(1).QueryTables(1) _
.Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=N UyHwYQI;"

Then you execute the query. You can record examples in Excel 98.

Here's an example of a query (not a real one)
With Selection.QueryTable
.Connection = "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
.Sql = Array("SELECT* & vbLf & "FROM `CUSTOMER.DBF` CUSTOMER" & vbLf
& "WHERE (CUSTOMER.CITY='London')" )
End With
Substitute your variable(s) in the query.

If you record a query from scratch you'll see how to bind the query result
to a specific cell or range of cells.

You might also check out how to run a Parameter query (the user prompt is
built into the query).

The Excel VB help files have lots of examples on this stuff.

-Jim


6) In worksheet 1 starting from say cell A3, I 'post' the result of the
1st
query.

7) The coding then moves on to query all the sales orders for this
customer
code between a date range using the same connection that was created in
point 5.

8) In worksheet 2 starting from say cell A3, I 'post' the result of the
2nd
query.

9) I do steps 7 and 8 again for 4 more queries with each query going into
a
new worksheet and all using the same connection.

10) Once these queries have been completed, I then close the connection
and
objects gracefully.

How can this be done via the Excel VBA version on a Mac (ie, 98 or 2001)??

Just as a side point, this querying will be done from a remote Mac
connecting to the PC/SQL Server via broadband, would it be more efficient
to
run each query manually rather than the user having to wait for all 6
queries to complete? Is there anyway that Excel could let them know how
far
the querying has gone?

Your assistance would be most appreciated.

Rgds

Laphan