0

I'm trying to return a data value from MySQL into a cell in Excel. There will only be a single row returned from SQL as the Where clause if against the Primary Key.

I can get the SQL to execute but don't know how to push the returned value into the Excel cell.

Sheet1 Cell C2 contains a postcode the a user can enter. I want to return a value of ABC from the database based upon the postcode into cell C5.

Here's my code:

Sub GetABCForPostcode()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

t = "table1"
iParam1 = Sheets("Sheet1").Range("$C$2").Value

strCon = "Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=world;UID=root;PWD=MyPassword"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("adodb.recordset")

rs.Open "SELECT abc FROM " & t & " WHERE postcode = '" & iParam1 & "'", cn

End Sub

The SQL works and doesn't return any errors but I don't know how to get the returned value into cell C5??? Any ideas welcome.

Thanks

NJQ
  • 3
  • 1
  • 3
  • See [CopyFromRecordset](https://msdn.microsoft.com/en-us/library/office/aa223845%28v=office.11%29.aspx?f=255&MSPPError=-2147217396). There's two examples of different ways there. – Scott Holtzman Oct 11 '17 at 16:25
  • check out the solution I have used before it may provide a different approach to what you are trying https://stackoverflow.com/a/45616221/8411349 – PeterH Oct 11 '17 at 16:25
  • The duplicate topics demonstrate different methods for displaying data in a worksheet from an ADODB recordset. That the underlying database is not MySQL in those questions is irrelevant from this question's point of view. – Shadow Oct 11 '17 at 16:36

0 Answers0