We help IT Professionals succeed at work.

how insert data from sql in a excel cell depending a cell excel value

hi

is it posible to get a data from a sql table linked to an excel spreadsheet

i have a file named minuta, and i have a sql server with a table named facturas where there are sales data registers.

i want to put the sale import taked from a sql table (dbo.facturacion) , where a field named Numdoc is equal to cell value a2

is it posible it?

thanks a lot
Comment
Watch Question

Commented:
Yes, this can absolutely be done.  You can do this two ways:
1.  Include the cell value in A2 in your SQL script.
2.  Loop over the "Numdoc" values in your recordset and write the other field values when you find a match.

There are pros and cons to each.

Including the "Numdoc" value from A2 in your script only allows one record to be returned.  Not the most effective if you have a lot of "Numdoc" values to look up.

Looping over the "Numdoc" values in your recordset would be more efficient with a lot of "Numdoc" values in your worksheet, but looping over the recordset multiple times is also not very efficient.

Author

Commented:
hi reitzen

thanks your comment

have you one exapmle?

i try to do it whitout success

thanks
sorry

Author

Commented:
hi chwong67

thanks your comments

i see reference you give me

i have a dude

how refer or obtain only a value depending a cell value , by example in this code:
With rsPubs
      ' Assign the Connection object.
      .ActiveConnection = cnPubs
      ' Extract the required records.
      .Open "SELECT * FROM Authors"
      ' Copy the records into cell A1 on Sheet1.
      Sheet1.Range("A1").CopyFromRecordset rsPubs
      
      ' Tidy up
      .Close
End With

...................................................................

i think it will be something like this:

With rsPubs
      ' Assign the Connection object.
      .ActiveConnection = cnPubs
      ' Extract the required records.
      .Open "SELECT * FROM Authors where idAuthor = " + "$A1"
      ' Copy the records into cell A1 on Sheet1.
      Sheet1.Range("A1").CopyFromRecordset rsPubs
      
      ' Tidy up
      .Close
End With

..............................................................

you know how can i do that?


thanks a lot
hi

i solve it

Function GetSupplierId(cell As Range)
    Dim importe  As Double
    Dim IdDoc As String
    IdDoc = ActiveCell.Offset(, -7)
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    GetSupplierId = rs.Fields(0).Value
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.