PDA

View Full Version : [SOLVED] How to get relevant data



JackkG
01-22-2015, 11:58 AM
Hi All,


Sheet1
......Name......CID....PID....RefID...Frequency
row1-John J....C1001...P004...R003.....1....[Button]
row2-Mike J....H1004...P019...R009.....1....[Button]





Sheet2
.......Name......CID.....PID...RefID...Frequency
row01-Jack M....C1001....P001...R001...0
row02-Jack M....C1001....P003...R002...0
row03-John J....C1001....P004...R003...1


row04-James H...H1002....P005...R005...0
row05-James H...H1002....P006...R006...0


row06-Mike J....J1009....P039...R009...0
row07-Mike J....J1009....P049...R009...0


row08-James H...H1004....P015...R005...0
row09-Mike J....H1004....P029...R009...0
row10-Mike J....H1004....P019...R009...1






I got 1000+ data in sheet2, some with unique CID values filtered. When I click on the button on sheet1, say button on row2, it should display the results row8 to row10 for that CID from sheet2 in listbox or some other way to display in a creative fashion.


Can someone help me out with this through Excel VBA code. Any help on this will be appreciated.


Thanks!


Jack

JKwan
01-22-2015, 01:38 PM
give this a go
you need to REFERENCE - Microsoft ActiveX Data Object 2.x library
this is SHEET1 module code


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Target.AddressLocal(False, False) = "B1" Then Exit Sub
If (Not Intersect(Target, Range("B:B")) Is Nothing) Then
CID = Target.Value
GetData
Else
CID = ""
End If
End Sub



This is MODULE1 code


Global CID As String
Sub GetData()
Dim sSQL As String
Dim stcon As String
Dim objConnection As ADODB.Connection
Dim objRecordSet As ADODB.Recordset
Dim WB As Workbook
Dim WS As Worksheet

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

stcon = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 8.0;"""
sSQL = "Select " & _
"[Name], [CID], [PID], [RefID], [Frequency] " & _
" From [Sheet2$] Where" & _
" [Sheet2$].[CID] = '" & CID & "';"
objRecordSet.Open sSQL, stcon, , adLockOptimistic
Set WB = ThisWorkbook
Set WS = WB.Worksheets("sheet1")
If Not objRecordSet.EOF Then
With WS
.Range("H2:L100").Clear
.Range("H2").CopyFromRecordset objRecordSet
End With
Else
MsgBox "CID - " & CID & " not found"
End If

Set WS = Nothing
Set WB = Nothing
Set objRecordSet = Nothing
End Sub

JackkG
01-22-2015, 01:41 PM
Hi JKwan,

Thanks for your reply. Will give it a try. Will be back soon. Thanks!

JKwan
01-22-2015, 01:49 PM
I am assuming that your data if located from column A to E, with headers of "Name, CID, PID, RefID, Frequency"

JackkG
01-22-2015, 01:56 PM
Hi JKwan,

Why it gives runtime error, saying "Cannot update. Database or object is read-only" at this line: objRecordSet.Open sSQL, stcon, , adLockOptimistic

JKwan
01-22-2015, 02:05 PM
is the sheet protected?

JKwan
01-22-2015, 02:08 PM
while i am waiting, attaching what i have

To run it, click on any cell in column B (CID) of sheet1 will trigger the data retrieval

JackkG
01-23-2015, 08:50 AM
Hi John, your attached workbook also gives the same read-only error.

JKwan
01-23-2015, 08:54 AM
Give it another go, by saving it onto local drive like drive c: then launch.

JackkG
01-23-2015, 09:00 AM
Nope...still same error. What can be the problem?

JKwan
01-23-2015, 09:13 AM
hmmm, I really don't know as to why!! I have no problem what so ever.

poking in the dark, was your Office recently updated? Heard MS screwed something up with Office update.

JackkG
01-23-2015, 09:16 AM
No, I didn't update it. Anyway, let's see if I come up with a solution to this will let you know, got to dig in more.

JKwan
01-23-2015, 09:18 AM
OK, maybe try to run the spread sheet from another machine?

JackkG
01-23-2015, 10:01 AM
Yep it worked. I provided full path to the xlsm file in Data source in the connection string.

So instead of "Data Source=" & ThisWorkbook.Name & ";" & _
I gave it as "Data Source=C:\Users\JG\Data Selection.xlsm;" & _

But was wondering what if the path changes, assume other users will be using this workbook in their machine, then how we can change this connection string so that any user can use this workbook?

JKwan
01-23-2015, 10:17 AM
update to this


stcon = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 8.0;"""

Tom Jones
01-23-2015, 10:18 AM
@JKwan,

Your code work good.

JKwan
01-23-2015, 10:20 AM
@Tom - thank you
@ Jack - I am glad that it is running, hoping that you can amend the code to work for you with your real data.

JackkG
01-23-2015, 10:23 AM
JK, that was perfect. It worked fine. Just one more thing. Is there anyway we can display the results in a listbox or something like that.

JKwan
01-23-2015, 11:01 AM
try this out

JackkG
01-23-2015, 11:31 AM
Wow!! That was great JK. Thanks a lot for all your help! really appreciate it. Thanks!!

JKwan
01-23-2015, 01:28 PM
Glad that is working for you

Have a great weekend.