Connection to SQL Server to create Data Models using VBA

[RodgerDJr]

Good afternoon all. I have been trying to create a VBA function that will allow my user to select which tables they would like to pull in to Excel as a Data Model so they can then use the table or tables in a Power Pivot.

I have recorded the steps a few times and it work fine when I record it but when I try to update the VBA with my variable I am not able to get it working correctly. I have the tables but there really is not a connection to them so when I try and create a Power Pivot I do not have any tables to select from.

Could my issue be I do not need the connection to connect to the SQL Server only my queries need to connect to my SQL Server?

Here is my Code

myServer = "WZF1A2F519156F\SQLEXPRESS"
myDatabase = "BHSF_North"
mySchema = "dbo"
myTableName = "BusinessUnit"
myConnectionString = "OLEDB;Provider=sqloledb;Data Source=" & myServer & ";Initial Catalog=" & myDatabase & ";Integrated Security=SSPI;"


'QUERY

ActiveWorkbook.Queries.Add Name:=myTableName, Formula:=
"let" & Chr(13) & "" & Chr(10) & " Source = Sql.Database(""" & myServer & """, """ & myDatabase & """)," & Chr(13) & "" & Chr(10) & " " & mySchema & "" & myTableName & " = Source{[Schema=""" & mySchema & """,Item=""" & myTableName & """]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " " & mySchema & "" & myTableName

'EXAMPLE This is what I recorded I have it so I can duplicate above
' ActiveWorkbook.Queries.Add Name:="Staff", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Sql.Database(""WZF1A2F519156F\SQLEXPRESS"", ""BHSF_North"")," & Chr(13) & "" & Chr(10) & " dbo_Staff = Source{[Schema=""dbo"",Item=""Staff""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " dboStaff"

'EXAMPLE This is for the text below to try and duplicate. I was doing this but it was not working and I saw an example where they used the properties and filled it in so I thought that might work better.
' Workbooks("Book1").Connections.Add2 "Query - Staff", _
"Connection to the 'Staff' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Staff;Extended Properties=" _
, """Staff""", 6, True, False

'CONNECTION This does not seem to do anything.
ThisWorkbook.Connections.Add2
Name:="SupplimentalTablesSQL",
Description:="BHSF North SQL Connections",
ConnectionString:=myConnectionString,
CommandText:=myTableName,
lCmdtype:=XlCmdType.xlCmdTableCollection,
CreateModelConnection:=True,
ImportRelationships:=True


The Staff(2) is the one that is generated when I select the Connection from the Menu system in Excel. I left them to show the difference the Connection only is the one my VBA code creates but does not work.

Thank you for your help,
Rodger

 

[Monty]

It seems like you're trying to dynamically create a connection to SQL Server tables in Excel using VBA. I'll guide you through a modified version of your code to help you achieve this.First, ensure you have the Power Pivot add-in enabled. Then, modify your VBA code as follows:

Sub CreatePowerPivotConnection()
    Dim myServer As String
    Dim myDatabase As String
    Dim mySchema As String
    Dim myTableName As String
    Dim myConnectionString As String
    Dim myQueryName As String

    ' Set your SQL Server connection details
    myServer = "WZF1A2F519156F\SQLEXPRESS"
    myDatabase = "BHSF_North"
    mySchema = "dbo"
    myTableName = "BusinessUnit"
    myConnectionString = "OLEDB;Provider=sqloledb;Data Source=" & myServer & _
                        ";Initial Catalog=" & myDatabase & ";Integrated Security=SSPI;"

    ' Set the name for your query
    myQueryName = myTableName

    ' Add the Power Query
    ActiveWorkbook.Queries.Add Name:=myQueryName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""" & myServer & """, """ & myDatabase & """)," & Chr(13) & "" & Chr(10) & "    " & mySchema & "" & myTableName & " = Source{[Schema=""" & mySchema & """,Item=""" & myTableName & """]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    " & mySchema & "" & myTableName

    ' Refresh the query
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.ListObjects.Add(0, myQueryName & "_imported_data").QueryTable.Refresh BackgroundQuery:=False

    ' Add the Power Pivot connection
    ThisWorkbook.Connections.Add2 Name:=myQueryName & "_Connection", _
        Description:="Connection to the '" & myQueryName & "' query in the workbook.", _
        ConnectionString:=myConnectionString, _
        CommandText:=myQueryName, _
        lCmdtype:=xlCmdTableCollection, _
        CreateModelConnection:=True, _
        ImportRelationships:=True
End Sub

This code creates a Power Query and a Power Pivot connection based on the specified SQL Server table. Make sure to run this macro from your Excel workbook with the Power Pivot add-in enabled.Note: Ensure that your SQL Server allows integrated security (SSPI) or provide the appropriate credentials in your connection string if needed