Hello. I'm trying to do something that I think should be fairly simple but can't seem to get the right info to get started. I have sql server 2000 running on server 2000. I'm trying to write a VB macro for ExcelXP that will get data from a sql server db and display it in the worksheet. Also, I want to be able to write back changes made in Excel to the sql server db. If anyone can point me to a good (basic) resource or give me some code to start with I would apprciate it very much. I have limited experience with VB but I've done a lot of php/mysql programming so I think I can pick it up. Thanks.
To import the data from SQL Server you have to go to the Data menu option => Import External Data and create a connection to the SQL Server that will return the data you require. To copy this process start recording a macro and you will be able to play with that later.
To update data in SQL Server from Excel you will need to create a reference (Excel Microsoft Visual Basic Editor) by going to the Tools menu option => References and setting a reference to something like "Microsoft ActiveX Data Objects 2.7 Library" (it is possible to download this from the microsoft site, it will include some help files with it). You can then make an ADODB Command that will use SQL to update the SQL Server tables. You must determine which records have been changed.
I hope that helps a bit or at least gives you an idea where to start looking.