Bind .NET recordset to Excel worksheet

[flynbye]

Just looking for a good example to bind some data from a SQL query that I'm running on our backend tables to an Excel workbook. I've done this before and don't remember it being that difficult but having trouble setting it up at the moment.

Anyone able to point me to any good examples I hope?

As always, "Thanks!"


I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[RonRepp]

I normally use a query table to bind data, and then the user can simply refresh the report through the External Data toolbar.

 SQL = GetSql  ''GetSql is a function returning a SQL string
        
    With XL.ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=_SERVER;UID=User;APP=Microsoft® Query;WSID=ComputerName;DATABASE=DB;Trusted_Connection=Yes" _
        , Destination:=XL.Range("A3"))
        .CommandText = SQL
        .Name = "Shipping"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

**Note: This is a VB6 version of the code. It would have to be updated (I think RefreshStyle is the only one). Search for xlInsertDeleteCells in Object Browser.

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.