Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
Hello everyone,
I created a Query in Excel 2000 to retrieve some data from Access 2000. I used the wizard to do it and I enter a filter (criteria)to bring only data for one customer at the time. I need to have the user selecting the customer from a dropdown list control(maybe)in Excel and use that result to refresh the query. I've looked for help in the KB with no success. I also try to record a macro but it gives me a very very long string that won't run again and it is very difficult to edit. Does anybody know how to accomplish this? I thank you all in advance for your help. Gustavo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
Hello Gustavo,
Here is a snippet of code that may help. You will likely have to live with the long string that is difficult to edit as you can see from the code. Do Until ActiveCell.Value = "" Counter = Counter + 1 Application.StatusBar = "Finding data for Lot " & Counter & " of " & BatchCount & " Lots. " Workbooks("new edi.xls").Activate Sheets("Shipments").Activate ItemNo = Left(ActiveCell.Value, 5) & Right(ActiveCell.Value, 2) PartNo = ActiveCell.Value LotNo = Trim(ActiveCell.Offset(0, 1).Value) ' Open connection cntSQL.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=testsql;Data Source=SQL" sSQL = "SELECT DESC_DAT.F_NAME, SGRP_EXT.F_TSNO, SGRP_EXT.F_VAL, TEST_DAT.F_NAME, SPEC_LIM.F_USL, SPEC_LIM.F_TAR, SPEC_LIM.F_LSL FROM att.dbo.DESC_DAT DESC_DAT, att.dbo.DESC_GRP DESC_GRP, att.dbo.PART_DAT PART_DAT, att.dbo.PRCS_DAT PRCS_DAT, att.dbo.SGRP_DSC SGRP_DSC, att.dbo.SGRP_EXT SGRP_EXT, att.dbo.SPEC_LIM SPEC_LIM, att.dbo.TEST_DAT TEST_DAT WHERE DESC_DAT.F_DESC = SGRP_DSC.F_DESC AND SGRP_EXT.F_SGRP = SGRP_DSC.F_SGRP AND PART_DAT.F_PART = SGRP_EXT.F_PART AND TEST_DAT.F_TEST = SGRP_EXT.F_TEST AND DESC_DAT.F_DSGP = DESC_GRP.F_DSGP AND PRCS_DAT.F_PRCS = SGRP_EXT.F_PRCS AND SPEC_LIM.F_PART = PART_DAT.F_PART AND SPEC_LIM.F_TEST = TEST_DAT.F_TEST AND (PRCS_DAT.F_NAME In ('Attachment Final Dimensions','Arm IP Production Dims','BP IP Production Dims','BP XIP Production Dims')) AND DESC_DAT.F_NAME='" & LotNo & "' AND PART_DAT.F_NAME='" & PartNo & "'" 'execute the SQL query Set cmdSQL.ActiveConnection = cntSQL cmdSQL.CommandText = sSQL Set rstSQL = cmdSQL.Execute() If rstSQL.EOF = True Then sSQL = "SELECT DESC_DAT.F_NAME, SGRP_EXT.F_TSNO, SGRP_EXT.F_VAL, TEST_DAT.F_NAME, SPEC_LIM.F_USL, SPEC_LIM.F_TAR, SPEC_LIM.F_LSL FROM att.dbo.DESC_DAT DESC_DAT, att.dbo.DESC_GRP DESC_GRP, att.dbo.PART_DAT PART_DAT, att.dbo.PRCS_DAT PRCS_DAT, att.dbo.SGRP_DSC SGRP_DSC, att.dbo.SGRP_EXT SGRP_EXT, att.dbo.SPEC_LIM SPEC_LIM, att.dbo.TEST_DAT TEST_DAT WHERE DESC_DAT.F_DESC = SGRP_DSC.F_DESC AND SGRP_EXT.F_SGRP = SGRP_DSC.F_SGRP AND PART_DAT.F_PART = SGRP_EXT.F_PART AND TEST_DAT.F_TEST = SGRP_EXT.F_TEST AND DESC_DAT.F_DSGP = DESC_GRP.F_DSGP AND PRCS_DAT.F_PRCS = SGRP_EXT.F_PRCS AND SPEC_LIM.F_PART = PART_DAT.F_PART AND SPEC_LIM.F_TEST = TEST_DAT.F_TEST AND (PRCS_DAT.F_NAME In ('Attachment Final Dimensions','Arm IP Production Dims','BP IP Production Dims','BP XIP Production Dims')) AND DESC_DAT.F_NAME='" & Left(LotNo, 7) & "' AND PART_DAT.F_NAME='" & PartNo & "'" Set cmdSQL.ActiveConnection = cntSQL cmdSQL.CommandText = sSQL 'execute the SQL query Set rstSQL = cmdSQL.Execute() CurrentWorkbook = FilePrefix & "_" & ItemNo & ".xls" Workbooks(CurrentWorkbook).Activate Worksheets("sheet3").Activate If rstSQL.EOF = True Then MsgBox Prompt:="There is no data for Lot " & LotNo & ", part number " & PartNo HTH, Greg "Gustavo" wrote in message ... Hello everyone, I created a Query in Excel 2000 to retrieve some data from Access 2000. I used the wizard to do it and I enter a filter (criteria)to bring only data for one customer at the time. I need to have the user selecting the customer from a dropdown list control(maybe)in Excel and use that result to refresh the query. I've looked for help in the KB with no success. I also try to record a macro but it gives me a very very long string that won't run again and it is very difficult to edit. Does anybody know how to accomplish this? I thank you all in advance for your help. Gustavo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
Gustavo,
The property that you want to change is the QueryTable .Sql property. This is the query string that is used to return the data from the database file. For example the current MSQuery query string may look something like this: SELECT Table1.fYear, Table1.fMonth, Table1.fCustomer FROM `C:\My Documents\Excel\Junk1\Access\Testdb1`.Table1 Table1 WHERE (Table1.fCustomer='myCustomer1') Below is some VBA code that will modify the example query string to include the name of the new customer (that you will need to provide). I'm not sure how familiar you are with the SQL language. Be careful to include spaces appropriately within the query string. Write back if you need help building your specific query string (below is an example which won't work for your database file). You can use the Get_Current_SQLstring subroutine below to get your current query string. You can use this information to construct the new query string. [Warning: The routine will overwrite the contents of Cell A1 on the first worksheet.] Troy Sub Get_Current_SQLstring() Dim qt1 As QueryTable Dim sSQL As String '''Get the current query string. Set qt1 = Worksheets(1).QueryTables(1) sSQL = qt1.Sql '''Copy the string to cell A1 on Sheet1. Worksheets(1).Range("A1").Value = sSQL End Sub Sub Change_qt1_SQLstring() Dim qt1 As QueryTable Dim sSQL As String Dim sCust As String '''=== Put your code here... === sCust = "myCustomer2" '''<== change this '''=== (Get the name of the customer the user selected) === '''Construct the new query string. sSQL = "SELECT Table1.fYear, Table1.fMonth, " & _ "Table1.fCustomer " & _ "FROM Table1 " & _ "WHERE (Table1.fCustomer = '" & _ sCust & "')" '''Print the query string to the debug window so that you can check it. Debug.Print sSQL '''Refresh the QueryTable using the new query string. ''' (This example assumes the QueryTable is ''' the first QT on the first worksheet.) Set qt1 = Worksheets(1).QueryTables(1) qt1.Sql = sSQL qt1.Refresh End Sub "Gustavo" wrote in message ... Hello everyone, I created a Query in Excel 2000 to retrieve some data from Access 2000. I used the wizard to do it and I enter a filter (criteria)to bring only data for one customer at the time. I need to have the user selecting the customer from a dropdown list control(maybe)in Excel and use that result to refresh the query. I've looked for help in the KB with no success. I also try to record a macro but it gives me a very very long string that won't run again and it is very difficult to edit. Does anybody know how to accomplish this? I thank you all in advance for your help. Gustavo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Problem with .Background Query option of ODBC Query | Excel Programming |