VBA Query coding in Ms Excel for Mac OS 9 (ping Jim)
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 |
VBA Query coding in Ms Excel for Mac OS 9 (ping Jim)
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 |
VBA Query coding in Ms Excel for Mac OS 9 (ping Jim)
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 |
uery
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 |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com