Retrieve data from Access

[Richard m]

I have a requirement to import data from MSAccess to Excel. I have done
this using VB. The problem is that I use a button on the spreadsheet
to run the macro.

I want to run the macro after I enter data in a cell and press the
enter key. Can this be done and how.

Thanks in advance

 

[Edward Stansfeld]

try using the SelectionChange event for the relevant worksheet.
Call the macro through here, instead of with the button OnClick event.
Edward

 

[Richard m]

Edward,

Where do I find this "SectionChange event for the relevant worksheet"?
I look and did not find anything. What did I miss

 

[Edward Stansfeld]

- Get in the VBA editor (press Alt and f11)
- look in the Project Explorer (Control+ r)
- you will see a VBA Project with your workbook name in brackets.
- Each sheet is listed below
- double click the sheet you want to work with, and a coding window will
come up.
- at the top of the new window is a box with "(General)" written in it.
- change (General) to "Worksheet" by selecting from the drop-down.
- some code will probably appear (the Change event?), which could be the
best event to use.
- you can create a SelectionChange event by selecting it on the other
drop-down menu
- type the code between the "Private Sub Worksheet_Change" and the "End Sub"
- e.g. (to turn the background of the selected cell black or yellow - undo
it with the background color button)

With Target.Interior
.Color = 6
End With

- experiment with Change and SelectionChange, to see which you prefer

Edward

 

[Richard m]

Thanks, I will be trying this tonight. one other thing, I will b
repeating this for each row for a different partno (building a table)
The following code is working when I click on a button. Note th
Access db has over 170,000 records in it.




Code
-------------------


Sub Macro3()

Dim Db As Database, Rs As Recordset
Dim Startcell As Range
Dim Criteria As String
Set Db = OpenDatabase("c:\MyFiles\Testdb.mdb")
Set Rs = Db.OpenRecordset("Parts")

Criteria = Range("a1").Value
Rs.MoveFirst

For x = 0 To Rs.RecordCount - 1

If Rs.Fields("partno").Value = Criteria Then


Range("f4").Value = Rs.Fields("partno").Value
Range("g4").Value = Rs.Fields("description").Value
Exit For


End If
Rs.MoveNext
Next

Rs.Close
Db.Close
End Sub

 

[Edward Stansfeld]

Let me know how you get on. From the size of your database, another approach
may be better.

Can you limit the table recordset in some way, to exclude partnos that you
will not need? If you can write an access query to limit the recordset to
under 65000 rows, you would be better off using MS Query to dump the
recordset into Excel, and then using VLOOKUP functions to grab your
descriptive data. It would be faster and cause less strain on your network.

But if the recordset is over 65,000 lines, this will not work.
Edward

 

[Richard m]

Edward,

I put the code in seclectionchange and I had to rewrite some code and
it worked great. Even selecting a part no at the bottom of the list
gave a pause but wasn't too slow. I am not sure if the parts table can
be split or filtered to reduce the size he needs to look in. I think
the numbers being pulled are very random. Again, thanks