Pulling SQL Server data into Excel

Question

I'm trying to pull data from an SQL Server database into Excel 2016 via ODBC / Microsoft Query (built in to Excel).

I'd like to have a dropdown of companies, where after a company is selected from the dropdown, all contacts for that company (from a separate contacts table) are displayed.

From what I've learned so far I need to use :field or @field to indicate that the user chooses - something like:

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))

But Excel / MS Query returns:

"Must declare the scalar variable "@CompanyName". Statement(s) could not be prepared."

Can anyone advise on how I declare the scalar variable? Assuming that's even what I'm actually needing to do here. Many thanks.

Answer 1

Have your declaration at the top.. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

DECLARE @CompanyName VARCHAR(200);
SET @CompanyName = 'CompanyName';

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))

Answer 2

In my mind there are a few ways you can achieve this, none of which are simple solutions. Which ever way you approach this, I think you will need to make two passes in to the database:

1) to get a list of current company names

2) to get all the contacts associated with chosen company.

First up you need to decide where/how you are going to store your company names. You could either store them in a local table, and provide a lookup via the CompanyName column using a Data Validation list option. This will mean that only the names in the list can be selected. Good for data integrity.

Or, if you don't want to store a local table then you will need to execute some vba code to connect to the database and download the company names. Iterate through the list of names and deliver the options to the user in whatever format you like.

Alternatively, you could manually write a list of names, but this will require additional maintenance when new company names are added (not ideal). Or, if you are really brave/mad, let the user write the company name in (I don't recommend this!!!!!).

Back to your original question, if you are using MS Query you should be able to parameterise your query in the query editor (if I remember correctly?!). Parameterise it such that the parameter value is taken from the cell where the user selects the company name. MS Query should do the rest for you... provided the query is parametrised client-side (i.e. in excel). Alternatively, you could once again go back to vba and dynamically create the query string in the code, passing in the CompanyName to replace @CompanyName and executing a full un-parametrised query.

If your query is stored server-side then perhaps create a stored procedure to accept @CompanyName as a parameter (you will need to write your DECLARE in your usp, as previously mentioned). Then, again using vba code, you can dynamically create the EXEC statement to execute against the connection.

These are just a couple of options, I'm sure there are others. But if you are looking for code as an answer, then I think you may be out of luck as there is more going on here than just "declare the scalar variable @CompanyName".

Answer 3

I would consider moving away from MS Query/ODBC in general, and start using PowerQuery and yes, learn some of the "M" Language too. Excel is moving away from ODBC (my hunch from other blogs. Nothing official yet) and old DLL-style technologies, so it's worth catching up and going from there. Also you might consider PowerBI and really step away from Excel.

However, to answer your question, I stick to tried and true VBA code:

Open Excel In a worksheet, drop an ActiveX control such as a combobox Go to Visual Basic (might need to display the Developer tab, in the ribbon) Go to the ThisWorkbook module Place a code similar to this:

Private Sub Workbook_Open()
    With Sheet1.cboxDROPDOWNLIST
        .Clear
        .TextAlign = fmTextAlignLeft
        .AddItem ("LIVE")
        .AddItem ("TEST")
        .Font.Name = "Arial"
        .Font.Size = 12
        .Font.Bold = True
    End With
End Sub

So in my example, I am opening the combobox called 'cboxDROPDOWNLIST', and clearing the list and adding two choices - LIVE and TEST

Next, double-click the combobox, and VBA will create a Change event.

Private Sub cboxDROPDOWNLIST_Change()
      'do your thing here
End Sub

So when ever the user changes their selection, the Change event kicks in, and you can control the logic from there.