Data Importing and Exporting

[Guest]

My users want to be able to use a Keyboard shortcut or have me add a button
on the toolbar that will allow them to do the following:

-I have a column of pri keys in column A of my spreadsheet,
-These pri keys have been imported from another source and have different
numbers of rows each time they are imported.
-From that column, they want it to hit a SQL Server DB that gets all the
other columns of information based on that pri key
-They do not want to upload the file to the server and they dont want to
manually go through DTS to do it themselves.

is there an easy way to get this done, such as a macro that loads up the
data to and then brings it back from SQL Server?

 

[Jamie Collins]

-I have a column of pri keys in column A of my spreadsheet,
-These pri keys have been imported from another source and have different
numbers of rows each time they are imported.
-From that column, they want it to hit a SQL Server DB that gets all the
other columns of information based on that pri key
-They do not want to upload the file to the server and they dont want to
manually go through DTS to do it themselves.

I think the proper way of doing this would be to create a stored
procedure on the server side that accepts a delimited string of PK
values as an argument and returns the required row set based on the PK
values passed. On the client (Excel side), you would compile the
string using the PK values in the worksheet, use e.g. ADO to create a
recordset by calling the stored proc, then read the data back to the
worksheet, filtering the recordset to match the existing order of PK
values in the workbook.

However, because Excel data may be accessed as a Jet data source,
there is an easier way. Jet has 'pass through' functionality which
allows you to JOIN an Excel table to a SQL Server table in a single
query. The following example should work for a connection to any xls
or .mdb:

SELECT T2.fname, T2.minit, T2.lname
FROM
[Excel 8.0;HDR=Yes;Database=C:\My
Folder\MyWorkbook.xls;].[employee$] T1
INNER JOIN
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee T2
ON T1.emp_id = T2.emp_id;

Jamie.

--