Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
enter db query results into cells
1. Will MS Query allow mw to use the query already built in Access?
Yes, if the query has no parameters , the query will show a table. I there are parameters, you'll you have to do that within MS Query though, not in the excel wizard, and it can be a bit tricky. ... Actually, i have just tried right now and i can't figure it out, but i am almost sure i 've done that ... maybe it was with ms sql server and not access. 2. To find the last cell of data in column A: Set RgD = WshD.Range("A65536").End(xlUp) 'or Set RgD = WshD.Range("A" & Wshd.Columns(1).Cells.Count).End(xlUp) 'Then to get the next cell bellow (blank) Set RgD = RgD.Offset(1,0) -- Regards, Sébastien <http://www.ondemandanalysis.com "RW" wrote: Thanks for the assistance. I have a few more questions. 1. Will MS Query allow mw to use the query already built in Access? 2. Since the sheet (Form in your example) will contain a lot of data, I will not know by cell name where the data goes. I'll need to somehow check and use the next row after all data. The spreadsheet has empty rows between each month of data so I would need to check from the bottom of the sheet. How is this done and how would I get that cell reference to base the copy to reference? I've seen some use offset to move around a worksheet and this would work for me as well once I get the data copy started. Thanks again "sebastienm" wrote: Hi, You could do something like: 1- Query in a hidden sheet 2- In the xl form, link the cells to that hidden sheet or copy cell value to the form 1. In a new sheet, say Query1, create your query: - menu Data Import (or Get) External Data New Database Query - follow the wizard using a Microsoft Access Database as data source - once the query built, hide the sheet. A macro to run the query would be: Sub RunQuery1() ThisWorkbook.Worksheets("Query1").Querytables(1).R efresh False 'not background End Sub 2. Say we want to run the query and copy data from sheet Query1 range A2:F7 to the form sheet, named Form. Sub RunForm() Dim WshO as Worksheet, WshD as Worksheet Dim RgO as range, rgD as range 'O for origin, D for Destination RunQuery1 '<----- Run the query 'Then make the multiple copy Set WshO=Thisworkbook.Worksheets("Query1") Set WshD=ThisWOrkbook.worksheets("Form") 'copy sample: Query1!A2 to Form!G1 Set rgO= wshO.Range("A2") Set rgD= wshd.range("G1") rgo.copy rgD ' or say copy multi-cells A2:A7 to G1:G6 wsho.range("A2:A7").copy wshd.range("G1") 'only 1st cell in dest needed End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com "RW" wrote: I have an Access query that will provide the data. The returned data is 6 fields and 6 rows. My plan is to access this query from Excel (using a button since I need to pass a month and year). However, this data needs to be placed in certain columns on the form. Field 1 in excel column 3, Field 2 into excel column 5, etc. I need to populate excel cell by cell and row by row with this data. Not simply put all of field 1 data into column 3, all of field 2 data in column 5, etc. This is because, I have a query in another database that will return 6 fields and 1 row. This data will need to be inserted between some of the data from the first query (after row 4 of 1st data). (maybe I could insert a new row into the worksheet between the query 1 data and insert the other query results?) How do you populate specified cells with data such as this? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
enter db query results into cells
Thanks for this assistance. I'll be working on it in the next fw days and now
have a great start. "sebastienm" wrote: 1. Will MS Query allow mw to use the query already built in Access? Yes, if the query has no parameters , the query will show a table. I there are parameters, you'll you have to do that within MS Query though, not in the excel wizard, and it can be a bit tricky. ... Actually, i have just tried right now and i can't figure it out, but i am almost sure i 've done that ... maybe it was with ms sql server and not access. 2. To find the last cell of data in column A: Set RgD = WshD.Range("A65536").End(xlUp) 'or Set RgD = WshD.Range("A" & Wshd.Columns(1).Cells.Count).End(xlUp) 'Then to get the next cell bellow (blank) Set RgD = RgD.Offset(1,0) -- Regards, Sébastien <http://www.ondemandanalysis.com "RW" wrote: Thanks for the assistance. I have a few more questions. 1. Will MS Query allow mw to use the query already built in Access? 2. Since the sheet (Form in your example) will contain a lot of data, I will not know by cell name where the data goes. I'll need to somehow check and use the next row after all data. The spreadsheet has empty rows between each month of data so I would need to check from the bottom of the sheet. How is this done and how would I get that cell reference to base the copy to reference? I've seen some use offset to move around a worksheet and this would work for me as well once I get the data copy started. Thanks again "sebastienm" wrote: Hi, You could do something like: 1- Query in a hidden sheet 2- In the xl form, link the cells to that hidden sheet or copy cell value to the form 1. In a new sheet, say Query1, create your query: - menu Data Import (or Get) External Data New Database Query - follow the wizard using a Microsoft Access Database as data source - once the query built, hide the sheet. A macro to run the query would be: Sub RunQuery1() ThisWorkbook.Worksheets("Query1").Querytables(1).R efresh False 'not background End Sub 2. Say we want to run the query and copy data from sheet Query1 range A2:F7 to the form sheet, named Form. Sub RunForm() Dim WshO as Worksheet, WshD as Worksheet Dim RgO as range, rgD as range 'O for origin, D for Destination RunQuery1 '<----- Run the query 'Then make the multiple copy Set WshO=Thisworkbook.Worksheets("Query1") Set WshD=ThisWOrkbook.worksheets("Form") 'copy sample: Query1!A2 to Form!G1 Set rgO= wshO.Range("A2") Set rgD= wshd.range("G1") rgo.copy rgD ' or say copy multi-cells A2:A7 to G1:G6 wsho.range("A2:A7").copy wshd.range("G1") 'only 1st cell in dest needed End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com "RW" wrote: I have an Access query that will provide the data. The returned data is 6 fields and 6 rows. My plan is to access this query from Excel (using a button since I need to pass a month and year). However, this data needs to be placed in certain columns on the form. Field 1 in excel column 3, Field 2 into excel column 5, etc. I need to populate excel cell by cell and row by row with this data. Not simply put all of field 1 data into column 3, all of field 2 data in column 5, etc. This is because, I have a query in another database that will return 6 fields and 1 row. This data will need to be inserted between some of the data from the first query (after row 4 of 1st data). (maybe I could insert a new row into the worksheet between the query 1 data and insert the other query results?) How do you populate specified cells with data such as this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula results display only after pressing Enter key | Excel Worksheet Functions | |||
I enter a formula but the results are not displayed | Excel Worksheet Functions | |||
What is this in Excel? © This is the results when I enter (c). | Excel Discussion (Misc queries) | |||
Enter results in another cell | Excel Discussion (Misc queries) | |||
Query results make entire sheet's cells move | Excel Programming |