Imprt data from SQL based on cell input
 

Dear All

I am trying to do alookup on an sql table based on data entered into and
excel cell and then import the information form the table. Whta I have is a
Inventory table in SQL. What I want to do is input the inventory code into
say A1 and then get the decription, type etc of that inveory item into cells
B1, C1, etc

I am able to get the connection to the database ok and use a select
statement to import data, but I need to add the data in A1 into the sql script

for example "Select * for authors where au-id = A1"

Can anyone help

Regards

Newman

below is the code I have so far

Sub Test()
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "Server=bhd;INITIAL CATALOG=pubs;" & _
strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors where au-id='sheet1.range("A1")'"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing


End Sub

Imprt data from SQL based on cell input
 

Hi

I think you want something like

..Open "SELECT * FROM Authors where au-id= '" & sheet1.range("A1") &
"' - if au-id is a string

or

..Open "SELECT * FROM Authors where au-id= " & sheet1.range("A1") &
" - if au-id is a number


hth

Keith

Imprt data from SQL based on cell input
 

You want to create a parameter query. In the sql statement, try using
a question mark for the criteria (parameter) that you want to pull
from your worksheet. You should be prompted to supply a value/
reference when you run the query.

Imprt data from SQL based on cell input
 

Thank You works

"Keith74" wrote:

Hi

I think you want something like

..Open "SELECT * FROM Authors where au-id= '" & sheet1.range("A1") &
"' - if au-id is a string

or

..Open "SELECT * FROM Authors where au-id= " & sheet1.range("A1") &
" - if au-id is a number


hth

Keith