[RESOLVED] Excel and Access

[LostAngel]

Hello, I have a table in access that has Name, Age, Address, Phone columns. I also have an excel worksheet that has headers for Name, Age, Address, Phone. I have never really done office development before, but how would I go about doing the following?

If I type the Name of someone under the name header, it will automatically pull the rest of the data associated with that name (Age, Address, Phone) from the access database...and put it in the 3 columns to the right of the name in the same row?

[kfcSmitty]

If you use VBA in excel, you could open an ADO connection with the query
"Select * from tablename where tablename = " & Cells(A,1)

or however you reference a cell in excel.

Then just grab the appropriate column from the resulting dataset

HTH

[Hack]

A problem you may encouter when running queries against this table is that Name is a reserved word in both VBA and VB.

If this project is in the beginning states, I would change that to something different, otherwise, in any query in which this field is mentioned, you would need to encapsulate it in brackets, i.e.: [name]

[LostAngel]

Alright, I have a simple example done...but what is the command to get the current cell your in?

[kfcSmitty]

I believe

ActiveCell.Value

would reference the current cell

[LostAngel]

ActiveCel.Value gives me the value of whats in the current cell (ie: "Age"). What I am looking for, is the cells location.

What I am looking for is, if I am in cell A2, and I type "Brandon" and hit enter...I want to know what row and col that I typed it in.

[si_the_geek]

Remove the .value and type the . again; you will then see a list of properties/methods relating to ActiveCell - one of which I believe is Row. I'm sure you can work out how to find the column too. ;)

[kfcSmitty]

I think

ActiveCell.Row
and
ActiveCell.Column

Would reference what you're looking for

I've never used VBA in excel though


*edit* wow, had the reply window open for 4 minutes before submitting aparently :afrog:

[LostAngel]

Thank you very much si_the_geek...I feel very dumb as I see the properties are very much like flexgrids and such...:(

[LostAngel]

This is my finished code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oRs As ADODB.Recordset
Dim oCnn As ADODB.Connection
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim i As Integer
'Connect to your Access db
Set oCnn = New ADODB.Connection
oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Documents and Settings\brandone\Desktop\db1.mdb;Persist Security Info=False"
oCnn.Open

'Create your recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT Name, Address, Phone, Age FROM tblInfo WHERE Name = '" & Me.Cells(ActiveCell.Row - 1, 1) & "'; ", oCnn, adOpenKeyset, adCmdText
'MsgBox ActiveCell.Row
'oApp.Visible = True

Sheet1.Cells(ActiveCell.Row - 1, 1).CopyFromRecordset oRs
oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
End Sub