Going beyond SELECT *

[TrippyTom]

Ok guys,

You have provided great samples of how you can extract data from a closed file. Now what do we do once we have that data?

Is it possible to create a mini-app that can be run from the desktop that uses SQL to extract data from an Excel file.

Take a phone list for example. For security purposes, we're not supposed to have any personal info accessible from our desks. So I would like to make it easy for my coworkers to have a phone list in the taskbar. It would act like a pop-up and have search and filter functionality. Is this asking too much of SQL?

This is getting exciting!

[Gert Jan]

You mean something like,
[vba]sSQL = "SELECT name,phonenumber FROM personal"[/vba]

Gert Jan

[malik641]

Took me all night (still a VB.NET newbie, and OleDB newb)...but here you go. It's working!!!

I made a very small VB.NET app to pretty much do what you're asking for. I wanted to include more functionality, but for the moment it is limited to use a worksheet that has to be formatted a certain way. Please have a look and let me know what you think.

NOTE: To locate the EXE file, just go to
ExcelWorkbook_SQL_Example\ExcelWorkbook_SQL_Example\bin\Release

Here is the code I used:

Custom Class:
[vba]Imports System.Data.OleDb

Public Class clsExcelSQL
' Author: Joseph
' Date: 9/5/2007
' Description: This class will be used to create the connection
' to a workbook, collect the data and pass it
' back to the derived class
Private m_strConnection As String
Private m_xlFileName As String
Private m_Conn As OleDbConnection
Private Const strSHEETNAME As String = "[Data$]"

Private Sub OpenConnection()
' Workbook should already be checked to see if
' it exists from the userform code. So there should
' be no error here.
Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_xlFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"""

Try
' Create new connection object and store it to global variable
m_Conn = New OleDbConnection(strConnect)
m_Conn.Open()
Catch ex As Exception
MessageBox.Show("An error occured with opening the connection:" & vbCrLf & vbCrLf & _
Err.Number.ToString & ": " & Err.Description.ToString, _
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Public Function RetrieveData(ByVal strSQL As String, ByVal strWorkbookName As String) As DataSet
' Retrieves the data based on the SQL expression
' Returns an ArrayList object
Dim ds As New DataSet
Dim da As OleDbDataAdapter

Try
' Open connection, then execute SQL command
m_xlFileName = strWorkbookName
Call OpenConnection()
da = New OleDbDataAdapter(strSQL, m_Conn)
da.Fill(dataSet:=ds, srcTable:=strSHEETNAME)
Catch ex As Exception
MessageBox.Show("An error occured with retrieving the data:" & vbCrLf & vbCrLf & _
Err.Number.ToString & ": " & Err.Description.ToString, _
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
m_Conn.Close()
End Try

Return ds
End Function

End Class[/vba]
Userform Class:
[vba]' NOTE: This code needs an Excel workbook to use with
' a worksheet named "Data" and for the headers to
' AT LEAST have: Last Name --- First Name --- Phone Number --- Email
' and it has to be spelled EXACTLY as shown

Public Class frmSQLExample
' Dimension Constant variables to use in SQL statement (workbook specific)
Private Const strCOLUMN1 As String = "Last Name"
Private Const strCOLUMN2 As String = "First Name"
Private Const strCOLUMN3 As String = "Phone Number"
Private Const strCOLUMN4 As String = "Email"
Private Const strSHEETNAME As String = "Data"

Private Sub cmdOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdOK.Click
' Use the Filter as a SQL WHERE clause (wildcards exceptable)

' ****First check if conditions are met*******************************************
If txtFileName.Text = "" Then
MessageBox.Show("No file entered to connect to!", "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

If My.Computer.FileSystem.FileExists(txtFileName.Text) = False Then
MessageBox.Show("File doesn't exist!", "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

If cmbFilterList.Text = "" AndAlso txtFilter.Text <> "" Then
MessageBox.Show("The search criteria will not be used as there" & vbCrLf & _
"is no filter to be used. Query will still execute.", _
"Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf cmbFilterList.Text <> "" AndAlso txtFilter.Text = "" Then
MessageBox.Show("The search criteria will not be used as there" & vbCrLf & _
"is no text to filter. Query will still execute.", _
"Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
' *************************************************************************** *****

Dim clsMyXLdata As New clsExcelSQL
Dim strSQL As String
Dim dsData As New DataSet

' Build SQL query
strSQL = "SELECT " & _
"[" & strCOLUMN1 & "]," & _
"[" & strCOLUMN2 & "]," & _
"[" & strCOLUMN3 & "]," & _
"[" & strCOLUMN4 & "]" & _
" FROM [" & strSHEETNAME & "$]"

If txtFilter.Text <> "" AndAlso cmbFilterList.Text <> "" Then
strSQL = strSQL & _
" WHERE [" & cmbFilterList.Text & "] Like '" & _
txtFilter.Text & "'"
End If

' Add ending semi-colon
strSQL = strSQL & " ORDER BY [" & strCOLUMN1 & "] ASC;"

' Execute query and collect data
dsData = clsMyXLdata.RetrieveData(strSQL, txtFileName.Text)

' Populate datagrid
dtgrdSQLdata.DataSource = dsData.Tables(0)
End Sub

Private Sub cmdGetFile_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdGetFile.Click
' Retrieves the filename to connect to
With Me.OpenFileDialog1
.CheckFileExists = True
.Multiselect = False
.ShowReadOnly = False
.Filter = "All Excel Files (*.xl*)"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
' Set the textbox to hold the file path
txtFileName.Text = .FileName
End If
End With
End Sub

Private Sub cmdCancel_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdCancel.Click
Me.Close()
End Sub
End Class[/vba]
I hope this is what you were looking for.

[malik641]

I just noticed that the OpenFileDialog doesn't work...I'll fix that tonight.

...I'll probably keep working on this tonight to add more functionality (I could use the practice!)

[malik641]

Got the OpenFileDialog working

I messed up the .Filter syntax.

[vba]Private Sub cmdGetFile_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdGetFile.Click
' Retrieves the filename to connect to
With Me.OpenFileDialog1
.CheckFileExists = True
.Multiselect = False
.ShowReadOnly = False
.Filter = "All Excel Files (*.xls*)|*.xls*"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
' Set the textbox to hold the file path
txtFileName.Text = .FileName
End If
End With
End Sub[/vba]

[TrippyTom]

Malik!

I'm excited about this. I'm sure it will take me all day to process this but thanks for posting it! I have a question about VB.NET.

I'm limited to what Office provides since I'm not allowed to install any 3rd party programs at work. Can I still use VB.NET in the native Office VBA coding window?

[malik641]

Your welcome Tom
If you need help with the code, please ask. I'd be glad to help.

To answer your question, no. You typically cannot just copy and paste VB.NET code into VBA since there are far more features in VB.NET that are not in VBA. This code can be 'converted' into VBA, yes. But you said you wanted a mini-app that ran from the desktop so I figured you wanted something to run on it's own rather than an office app.


I would talk to your job if I were you because you can easily get VB.NET 2005 express which is free and is a Microsoft product and your job shouldn't really have a problem with it (as long as you speak with your IT people, they should know the deals).

[TrippyTom]

Hey Joseph,

I had a peek on Microsoft's website and they have so many different names I got confused as to which software to download. Is "Visual Studio" the same as VB.NET?

I want to download the correct version and try it at home first before I approach my boss about it. I think I will have higher success in convincing her to let me use it at work if I know what I'm talking about.