Import data from SQL server into excel

[jebenexcel]

Hello,

I need to import data from an SQL database into an excel spreadsheet.

The query works fine in Microsoft SQL Management Studio, but I don't know how to get VBA to paste it into cell A1 of Sheet1.

I have already created the connection using data-> From other Sources->from SQL server.

This code used to do it, but I have to change it to a different server with different column names and now it doesn't work.

Sub RefreshSQL()


    Dim newsqltext As String
    
    newsqltext = "(query)"
    
    With ActiveWorkbook.Connections("SQL connection").OLEDBConnection
        .CommandText = newsqltext
    End With
    
    ActiveWorkbook.RefreshAll ' Refresh
    
End Sub

 

[jebenexcel]

Okay, so i figured out the code just changes the command text for an already existing connection. I changed the connection's source and just have to find a way to dynamically change the connection command text.
Any ideas?