How: creation of an interface for data query of a workbook

[alexiss]

Good evening,

Is there any way to query a simple but very long student record database created in excel?

It contains student personal details + the workshops they's attended. all records are - one row for student.

i wanted to ask if there is a way for an interface that can be built, with which the user could query the entire database, for students who (for example) haven't attended one of the workshops, and display them on a separate workbook ?

Regards, and thank you!

 

[bertie]

Hi and welcome to the forum.

I have used this simple dataset as a testbed for the code.

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:115px;"><col style="width:71px;"><col style="width:72px;"><col style="width:71px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:36px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-align:center; ">Student id</td><td style="font-weight:bold; ">Student Name</td><td style="font-weight:bold; text-align:center; ">Workshop A</td><td style="font-weight:bold; text-align:center; ">Workshop B</td><td style="font-weight:bold; text-align:center; ">Workshop C</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">1</td><td>aaa</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td><td style="text-align:center; ">n</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">2</td><td>bbb</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">3</td><td>ccc</td><td style="text-align:center; ">n</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">4</td><td>ddd</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td><td style="text-align:center; ">y</td></tr></tbody></table>

The way the code works is that you click anywhere inside the column you want to test and then run the code.
The code can be run from Excel via the Tools => Macro => Macros menu.
Or you can assign the macro to a button.

First the code gets sets up the column to process.

  [COLOR=darkblue]Set[/COLOR] rng = Selection
  col = rng.Column

I have assumed the data is on "Sheet1"; you can edit this.
We get the last row of the data set.
And the name we will call the sheet, i.e., the workshop name from the first row.

  [COLOR=darkblue]Set[/COLOR] wsSheet1 = Sheets("[COLOR=Red]Sheet1[/COLOR]")
  [COLOR=darkblue]With[/COLOR] wsSheet1
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    sheetName = .Cells([COLOR=Red]1[/COLOR], rng.Column).Value
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Then we add a new worksheet, deleting any existing sheets with same name.

  [COLOR=green]'you may want to delete any existing worksheet[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Application.DisplayAlertsalerts = [COLOR=darkblue]False[/COLOR]
      Sheets(sheetName).Delete
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = sheetName
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
  [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sheetName)
  wsNew.Range("A1").Value = "Students Name"

Then we loop through the data set and transfer the names of the students who didn't attend the workshop into the new sheet.

  [COLOR=green]'populate the new worksheet[/COLOR]
  counter = 1
  [COLOR=darkblue]For[/COLOR] rw = [COLOR=Red]2[/COLOR] [COLOR=darkblue]To[/COLOR] lr
    [COLOR=green]'==============================================[/COLOR]
    [COLOR=green]'edit for missed workshop value, currently "N"[/COLOR]
    [COLOR=green]'==============================================[/COLOR]
    [COLOR=darkblue]If[/COLOR] UCase(wsSheet1.Cells(rw, col)) = "[COLOR=Red]N[/COLOR]" [COLOR=darkblue]Then[/COLOR]
      counter = counter + 1
      [COLOR=green]'===========================================================[/COLOR]
      [COLOR=green]'edit the column in sheet1 for the student name, currently B[/COLOR]
      [COLOR=green]'===========================================================[/COLOR]
      wsNew.Range("A" & counter).Value = wsSheet1.Range("[COLOR=Red]B[/COLOR]" & rw).Value
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]Next[/COLOR] rw

And finish off by creating a new workbook.

  [COLOR=green]'create new workbook[/COLOR]
  Sheets(sheetName).Copy
  ActiveWorkbook.SaveAs "[COLOR=Red]C:\[/COLOR]" & sheetName & ".xls"

The full code is shown below.
Make a copy of your workbook.
Open the copy and press Alt + F11 to open the VBA Editor.
Double click of the ThisWorkbook module in the Project Window on the left hand side.
Edit the code where highlighted.
Copy and paste the code into the ThisWorkbook module.

To run, click on the workshop you want to process and run the macro from the Tools => Macro => Macros menu.

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] MissedWorkshop()
  [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
  [COLOR=darkblue]Dim[/COLOR] lr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] sheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] wsSheet1 [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  
  [COLOR=darkblue]Set[/COLOR] rng = Selection
  col = rng.Column
  
  [COLOR=darkblue]Set[/COLOR] wsSheet1 = Sheets("[COLOR=Red]Sheet1[/COLOR]")
  [COLOR=darkblue]With[/COLOR] wsSheet1
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    sheetName = .Cells([COLOR=Red]1[/COLOR], rng.Column).Value
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

  [COLOR=green]'you may want to delete any existing worksheet[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Application.DisplayAlertsalerts = [COLOR=darkblue]False[/COLOR]
      Sheets(sheetName).Delete
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = sheetName
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
  [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sheetName)
  wsNew.Range("A1").Value = "Students Name"
  
  [COLOR=green]'populate the new worksheet[/COLOR]
  counter = 1
  [COLOR=darkblue]For[/COLOR] rw = [COLOR=Red]2 [/COLOR][COLOR=darkblue]To[/COLOR] lr
    [COLOR=green]'==============================================[/COLOR]
    [COLOR=green]'edit for missed workshop value, currently "N"[/COLOR]
    [COLOR=green]'==============================================[/COLOR]
    [COLOR=darkblue]If[/COLOR] UCase(wsSheet1.Cells(rw, col)) = "[COLOR=Red]N[/COLOR]" [COLOR=darkblue]Then[/COLOR]
      counter = counter + 1
      [COLOR=green]'===========================================================[/COLOR]
      [COLOR=green]'edit the column in sheet1 for the student name, currently B[/COLOR]
      [COLOR=green]'===========================================================[/COLOR]
      wsNew.Range("A" & counter).Value = wsSheet1.Range("[COLOR=Red]B[/COLOR]" & rw).Value
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]Next[/COLOR] rw
    
  [COLOR=green]'create new workbook[/COLOR]
  Sheets(sheetName).Copy
  ActiveWorkbook.SaveAs "[COLOR=Red]C:\[/COLOR]" & sheetName & ".xls"
  
  [COLOR=green]'delete the sheet[/COLOR]
  [COLOR=green]'Application.DisplayAlerts = False[/COLOR]
  [COLOR=green]'  Sheets(sheetName).Delete[/COLOR]
  [COLOR=green]'Application.DisplayAlerts = True[/COLOR]
  
  [COLOR=green]'tidy up[/COLOR]
  [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wsSheet1 = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Ruddles]

Could you set up filters on the columns you're interest in and use those?