How to use Excel with SQL Server linked servers and distributed queries; How to import data from Excel to SQL Server; Prerequisite - Save Excel data as text. To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - first you have to export your Excel data to a text file.
You can use Get External Data (dispite its name), located in the 'Data' tab of Excel 2010, to set up a connection in a workbook to query data from itself. Use From Other Sources From Microsoft Query to connect to Excel Once set up you can use VBA to manipulate the connection to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doen't require a save to refresh the latest data. Here's a quick Sub to demonstrate accessing the connection objects Sub DemoConnection Dim c As Connections Dim wb As Workbook Dim i As Long Dim strSQL As String Set wb = ActiveWorkbook Set c = wb.Connections For i = 1 To c.Count ' Reresh the data c(i).Refresh ' view the SQL query strSQL = c(i).ODBCConnection.CommandText MsgBox strSQL Next End Sub. Sometimes SUMIF can get the job done.
![Data Data](/uploads/1/2/5/5/125588687/994407576.png)
Suppose you have a sheet of product information, including unique productID in column A and unit price in column P. And a sheet of purchase order entries with product IDs in column A, and you want column T to calculate the unit price for the entry. The following formula will do the trick in cell Entries!T2 and can be copied to the other cells in the same column. =SUMIF(Products!$A$2:$A$9999,Entries!$A2, Products!$P$2:$9999) Then you could have another column with number of items per entry and multiply it with the unit price to get total cost for the entry.