0

I am somewhat new to Excel VBA. I am working on an existing Excel worksheet that retrieves data from a SQL Server table via a stored procedure. It currently just displays the data on the spreadsheet. I want to be able to sort it by a value (either 0 or 1) and print the results as groups, first group 0, then group 1, and repeat the headers between groups. Thanks

Jim
  • 1
  • Jim, give some examples of what you have tried so far. That will encourage people to help more. – pczeus Feb 27 '16 at 17:39

1 Answers1

1

Ask SQL to do that for you: either change the stored procedure on the server to allow for sorting parameters.

Alternatively, you can also store the result of the stored procedure in a table variable and sort the result there (if you are not allowed to change the stored procedure. This would work like this:

declare @t table
    (
     Column1 int,
     Column2 bit,
     Column3 nvarchar(100),
     Column4 nvarchar(100),
     Column5 nvarchar(100),
     Column6 int,
     Column7 nvarchar(100),
     Column8 nvarchar(max),
     Column9 bit
    )

insert  into @t
        exec sys.sp_who

select  *
from    @t as t
order by Column3

As requested in your comment, I elaborate a bit on the answer. The above code is T-SQL: https://en.wikipedia.org/wiki/Transact-SQL If you would like to learn SQL then the following website can be of great help: http://www.w3schools.com/sql/default.asp

Basically, T-SQL (the above code) is what you send to an SQL Server in order to tell the server what kind of data you would like to retrieve. In other words, this is what Excel sends to the server and gets data in return. There are several different ways to accomplish that:

  1. The first possibility is to use the Data tab in Excel directly as described in the following article: http://kildrummy.com/how-to-pulling-export-data-into-excel/ Note, the screen shot at the bottom with the window called Connection Properties. You can clearly see the Command Type being set to SQL and the Command Text is SQL code (like the example I gave you above).
  2. The second option to get the data from the SQL Server would be VBA. The following Q&A is an example how to accomplish that: Accessing SQL Database in Excel-VBA Once again, you can note in the VBA the `strSQL´ string in the VBA code, which contains the SQL command being sent to the SQL Server.
Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • What language is this? My VBA2008/6.5 doesn't know it. – Paul Ogilvie Feb 27 '16 at 18:27
  • @PaulOgilvie I expanded upon my answer in order to show you how to use / implement the solution in your Excel workbook. Yet, you should know that the above code is merely an example. I do not know the name of your server nor the data you are getting from the server (or the stored procedure). Hence, you will have to adapt the above code **example** to match the actual data you would like to retrieve. – Ralph Feb 27 '16 at 20:17
  • Thanks, I am not sure how to add the code here, it doesn't fit in the comment section (sorry, complete nub to this forum. The data is retrieved and displayed okay, just all in one section. I want to sort it by a field and have it displayed in two groups, first by value 0, then by those with value 1, with the headers repeated (and an added header of the field description – Jim Feb 28 '16 at 18:36
  • I understood you the first time you wrote all of this in your question. But apparently you don't understand the answer. But you will understand if you read it and all referenced websites. – Ralph Feb 28 '16 at 18:55
  • Okay, I re-read through the answers, went to the links and read those and still don't understand how that relates to my grouping and displaying questions. Guess I am in the wrong forum. – Jim Feb 29 '16 at 16:50
  • Sorry, sent it to .com instead of .info, just resent – Jim Feb 29 '16 at 18:25
  • Correct, the Import button initiates the whole process, the VBA function Sub MBRS_ASSIGNED_PCP_PRINT(pcp As String, wPRINT As String) is the one that I am wanting to modify – Jim Feb 29 '16 at 18:44
  • Actually, you have already T-SQL code in your Excel file. Maybe someone else wrote the code and therefore you didn't notice. Look at `Sub Retrieve_UNIQUE_PCP()` and notice the `strSQL` string (which contains T-SQL code). This is exactly the same as I referenced in http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba. The answer provided by David Walker also contains a string `strSQL` which is stuffed with SQL command, which is then returned to the SQL Server for processing. – Ralph Feb 29 '16 at 18:49
  • Maybe I wasn't being clear, the section that I am trying to modify is Sec D of the aforementioned function (PRINT RESULTS). Currently it prints all records, I want to have it print records with a value of "0" first, then a new set of headers and records with a value of "1" after that. So that it is clear to the recipient with ones are for each value. – Jim Feb 29 '16 at 19:06
  • Now I understand the problem: you neglected to mention that only **some** of the data you wish to sort and filter by is in the SQL Server. Some data is on the sheet "PCP_List" in Excel only. Yet, if you get the data already sorted (the way you want it to be) from the server then the sheet "PCP_List" will be sorted for the printing in the order you want it to be. Yet, it would be better practice to write back to the SQL server the data which is (currently) only in the Excel file and update the table on SQL server accordingly. Like this you could proceed with the suggested answer. – Ralph Feb 29 '16 at 19:23
  • On a side note: there is no stored procedure on the SQL server via which you are getting the data into Excel. Your Excel file merely sends a simple `SELECT` statement in plain T-SQL code to the SQL server. – Ralph Feb 29 '16 at 19:25