G
Guest
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
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