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