Using SQL Server/Database Source from Cell Values

Question

I have a macro to query SQL data into multiple sheets, and the problem I encounter is that I want to allow the code to reference the SQL server address and database name from cell values (i.e. User inputs which server/database they want to connect and pull data from)

The current code with a fixed server/db name (SQLEXPRESS/Datalogger) is:

ActiveWorkbook.Queries.Add Name:="DataTable", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database("".\SQLEXPRESS"", ""Datalogger"")," & Chr(13) & "" & Chr(10) & "    dbo_DataTable = Source{[Schema=""dbo"",Item=""DataTable""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    dbo_DataTable"
Sheets.Add.Name = "DataTable"
Sheets("DataTable").Move After:=Sheets("HOME")

The area where the user can specify the server/db name would be in cells B1 & B2. So I tried the following code to add as a string, but it's not working:

Dim sSqlSvr As String
Dim sSqlDb As String
sSqlSvr = Worksheets("HOME").Range("B1")
sSqlDb = Worksheets("HOME").Range("B2")

ActiveWorkbook.Queries.Add Name:="DataTable", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(sSqlSvr, sSqlDb)," & Chr(13) & "" & Chr(10) & "    dbo_DataTable = Source{[Schema=""dbo"",Item=""DataTable""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    dbo_DataTable"
Sheets.Add.Name = "DataTable"
Sheets("DataTable").Move After:=Sheets("HOME")

I'm assuming I'm missing something here in calling the string. The error is Run-time error '1004'. "Couldn't get data from the Data Model". "The import sSqlSvr matches no exports. Did you miss a module reference?"

I guess the its literally looking for a server/db name with sSqlSvr/sSqlDb instead of recognizing as a string... any suggestions?

andrew · Accepted Answer · 2018-02-09 06:29:01Z

1

You have the variable names as literals within the string. You need to escape out of the string to insert the value of the variables into the string:

ActiveWorkbook.Queries.Add Name:="DataTable", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""" & sSqlSvr & """, """ & sSqlDb & """)," & Chr(13) & "" & Chr(10) & "    dbo_DataTable = Source{[Schema=""dbo"",Item=""DataTable""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    dbo_DataTable"

Edit: fixed double quotes!

edited Feb 9, 2018 at 6:29

answered Feb 8, 2018 at 0:54

andrew

1,8262 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joshjayse Over a year ago

Something else went wrong, I think its pretty close tho.... Expression.Error: Token Literal expected. Details: let Source = Sql.Database(.\SQLEXPRESS, Datalogger), dbo_DataTable = Source{[Schema="dbo",Item="DataTable"]}[Data] in dbo_DataTable Missing the "" perhaps? I tried variations such as (" & "sSqlSvr" & ", " & "sSqlDb" & ") and ("" & sSqlSvr & "", "" & sSqlDb & "") but it's not correct.... Expression.Error: The import sSqlSvr matches no exports. Did you miss a module reference?

andrew Over a year ago

My bad. The double quotes need escaping in the string. The joys of escaping!

Collectives™ on Stack Overflow

Using SQL Server/Database Source from Cell Values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related