Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Excel DB front-end with listboxes and comboboxes (Read 8445 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Excel DB front-end with listboxes and comboboxes
26.01.09 at 10:41:49
 
Excel DB front-end with listboxes and comboboxes
 
I have built an example how to use listboxes and comboboxes with SQL*XL to make a database front-end. I have attached the example spreadsheet to this topic but will explain in detail the code that I have written/recorded.  
 
I must admit to have lost the feature to fetch directly into listboxes etc but I have fixed the problems in version 4.3.12. Please ensure your version is up to date. Also I expect you to make a database connection manually. It is easily automated by recording the connection process as a macro. I have used the NWind Access database. If you do not have a copy you can download the free example databases collection from www.oraxcel.com/downloads.
 
Back to top
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Excel DB front-end with listboxes and combobox
Reply #1 - 26.01.09 at 10:43:04
 
Explanation:
 
The process works as follows. First a list of employees is retrieved. The employee names are put in a combobox.
 
Selecting an employee from the employees combobox the order list will be filled.
 
Selecting an order the order details will be retrieved and shown in the spreadsheet.
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Excel DB front-end with listboxes and combobox
Reply #2 - 26.01.09 at 10:45:48
 
Code used:
 
On sheet1 I added some controls. View the Control Toolbar and select the design mode. I created (you can draw them) a listbox and a combobox and named them lstOrders and cbxEmployees. A button was created to populate the Employees combo.
 
The following code was written in module Sheet1:
 
Code:
Private Sub cmdGetEmployees_Click()
  getEmployees
  lstOrders.Clear
  'Next lines is for redrawing the listbox.
  lstOrders.Visible = False
  lstOrders.Visible = True
End Sub
 


The procedure getEmployees will do everything that is needed to populate the employees combobox.
Then I need to clear the Orders list as after populating the employees list no orders should be selected anymore.
I noticed that the listbox does not refresh automatically and to force Excel to refresh I set visible to false and then to true again. A small trick...
 
Code:
Private Sub cbxEmployees_Change()
  getOrders (cbxEmployees.Text)
  'Next lines is for redrawing the listbox.
  lstOrders.Visible = False
  lstOrders.Visible = True
End Sub
 


When an item in the Employees combobox is selected this event is firing.  
I call the getOrders procedure that will be defined later to get the orders for the selected employee.
Again the orders list does not refresh and the visible trick is performed again.
 
Code:
Private Sub lstOrders_Click()
  Dim strOrder As String
  Dim strOrderId As String
  
  strOrder = lstOrders.Text
  strOrderId = Trim(Left(strOrder, InStr(1, strOrder, "(") - 1))
  getOrderDetails strOrderId
  ActiveWindow.ScrollIntoView 0, lstOrders.Top, 0, 0
  
End Sub
 


Finally the order details need to be shown when the user selects an order.
The click event is firing on the Orders listbox.
First I am getting the Order ID from the selected item.
I search with instr for the first occurrence of the bracket.  
Then I am taking the left x-1 characters where x is the position where the bracket was found.
Further I am trimming the spaces from the found string to be only left with the number.
The OrderId is fed into the getOrderDetails procedure which I will define below.
The Activewindow.ScrollIntoView command is needed to view the results and listbox after the fetch of the details.
Back to top
 
« Last Edit: 26.01.09 at 10:51:12 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Excel DB front-end with listboxes and combobox
Reply #3 - 26.01.09 at 10:51:56
 
Code in module1 to do all the database work:
 
Most of this code is recorded using the macro recorder. Here and there I put in an extra statement or changed one.
 
Code:
Sub getEmployees()
  SQLXL.Sql.SetText "select lastname from employees order by lastname to cbxEmployees;  "
  SQLXL.Targets(litExcel).StartFromCell = "A1"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub
 


GetEmployees will retrieve a list of employees and populates the combobox cbxEmployees.
All that is needed is to specify the select statement adding the "to listbox" at the end of the SQL command.
The StartFromCell is needed to ensure SQL*XL does not change the view to another part of the sheet.
We do not want to see any dialogs when we execute so they are set to not show.
Executing the statement is the last we need to do.
All of this is recorded apart from the StartFromCell command which I inserted manually.
 
Code:
Sub getOrders(Employee As String)
  SQLXL.Sql.SetText "select orderid & ' (' & customerid & ',' & orderdate & ')' from orders where employeeid = (select employeeid from employees where lastname = '" & Employee & "') to lstOrders"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub
 


The getOrders sub will retrieve all orders for the selected employee. The employee name is passed in as a parameter.
First the select statement needs to be build. I am concatenating the orderid field with the customerid and orderdate putting the last two in brackets and with a comma in between. This is purely for display purposes.
Note that in the SQL I have put the to listbox directive which will cause SQL*XL to put the results into the named listbox.
Again, we don't want to see any dialogs and the last thing to do is to execute the statement.
 
Code:
Sub getOrderDetails(OrderId As String)
  SQLXL.Sql.SetText "select * from orders where orderid = " & OrderId
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = False
    .Headings = True
    .Sort = False
    .StartFromCell = "$B$15"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub
 


The getOrderDetails sub is a little longer but it is actually a fully recorded statement so nearly no work was needed to fetch the order details.
The selected OrderId is passed into this sub. We already did the hard work to get the OrderId from the display item in the Listbox_Click event.
We use the OrderId in the SQL. This was done manually.
All of the next lines are recorded lines of code.
The output target is set to Excel and the optimiser is set for small queries. We will fetch only 1 row.
Next some properties of the Excel output target are set. I left all of these to the defaults except for the AutoFit as I manually set the columns to the correct width already.
I specified to not show any dialogs and the last thing to do is to execute the SQL.
 
I hope this shows how easy it is to make an Excel front-end to your databases.
Record most of the SQL executions. Build small procedures and tweak the SQL a little.
That's all.
 
An alternative approach is to use Excel cell formulas.
Back to top
 
« Last Edit: 26.01.09 at 11:02:20 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1