enter db query results into cells

[Guest]

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

 

[Guest]

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).Refresh 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

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

Thanks for this assistance. I'll be working on it in the next fw days and now
have a great start.