Export Excel Table to SQL Server through ODBC Connection in Visual Basic

[medberg]

Hello,
This is my first post so if anything is wrong with it I apoligize in advance. I have looked everywhere and come up empty handed trying to make a macro that exports an excel table to SQL server through an odbc connection. As of right now, I currently am able to access the database and import a particular table to my spreadsheet. The code that makes this connection is here:

With Sheet2.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=" + DSN + ";Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=" + DATABASE + ";" _
        , Destination:=Range("Sheet2!$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT * FROM " + DATABASE + ".dbo." + TABLE + " " + TABLE _
        )

Now what I want to do is set up something similar to go the other direction, using INSERT statements to populate a database table with the table that had been imported into Excel. Any help in setting up the initial connection or a reference would be great. It seems there is a lot of information out there for other connection types and scenarios, but nothing I could find on this. Thanks a lot.

 

[krysolov]

I believe what you're trying to do is explained in http://support.microsoft.com/kb/321686 as one of the options.

Personally, for security reasons, I let SQL server do the pull from spreadsheets. , instead of allowing spreadsheet to write to SQL, that process is pretty simple and straightforward. However, your environment may be different, so hopefully that article will help you out.