Best method of linking Excel 07 to SQL Server 2008R2

[Lightwave]

Ok

I want to create pivot in SQL Server 2008 and link it to an excel 07 worksheet.

And I kind of want that download into excel to be fired by a vba function.

I'm guessing create a view and then use the connection manager.
Ok that seems fine.

But how do you trigger that load from a VBA excel function?

Thanks

 

[Lightwave]

Right just writing up my notes on this managed to get a working solution.
(which I guess is a best method at the moment for me!)

Open up excel 07 and ensure that the developer tab is enabled by going to

Big button in top left
Excel Options
tick - Show Developer tab in the ribbon

Now
Hit the developer tab and hit the Visual basic button top left.
Create New Module
(I called this GetSQLinfo)

I created the following function

Sub Connect2SQL2008R2()

    Dim oCon As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Set oCon = New ADODB.Connection
    
    oCon.ConnectionString = "Driver=SQL Server;Server=EU-AUF-01;UID=smith.john;Database=PanaceaCureAll; Trusted_Connection=yes;"
    oCon.Open
    
    Set oRS = New ADODB.Recordset
    oRS.ActiveConnection = oCon
    oRS.Source = "Select * From Table1"
    oRS.Open
    Range("A2").CopyFromRecordset oRS
    oRS.Close
    oCon.Close
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing

End Sub

Ensure within Tools References you have

Microsoft ActiveX Data Objects 2.8 Library

selected

This placed Table1 information
of Database PanaceaCureAll
From the server EU-AUF-01
using the privileges of User smith.john

in the active sheet starting at cell A2

Remember before trying this ensure that you have been given the apporpriate privileges by the DBA to access the database on the server instance.

Note you will need to save the spreadsheet as an xslm

I.e spreadsheet with macros