Consulting

Results 1 to 18 of 18

Thread: Going beyond SELECT *

  1. #1
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location

    Going beyond SELECT *

    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!
    Office 2010, Windows 7
    goal: to learn the most efficient way

  2. #2
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    You mean something like,
    [vba]sSQL = "SELECT name,phonenumber FROM personal"[/vba]

    Gert Jan

  3. #3
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  4. #4
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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!)




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  5. #5
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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]




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  6. #6
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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?
    Office 2010, Windows 7
    goal: to learn the most efficient way

  7. #7
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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).




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  8. #8
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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.
    Office 2010, Windows 7
    goal: to learn the most efficient way

  9. #9
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Hey Tom,

    Perfectly understandable. You should definitely become familiar with Visual Studio .NET (the free ones are Express Editions). Check out the following link:
    http://msdn2.microsoft.com/en-us/express/aa718406.aspx


    Visual Studio 2005 (.NET) [non-express editions AFAIK] can hold J# .NET, VB .NET, C# .NET, and others all in one program. The express editions are separate downloads (and they're free ). You can find all of them at the following:
    http://msdn2.microsoft.com/en-us/express/aa975050.aspx


    After that you should be on your way!

    Good luck Tom




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  10. #10
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    Ok Joseph, installed it now. I can't find the .xls file in the zip?
    Office 2010, Windows 7
    goal: to learn the most efficient way

  11. #11
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Sorry Tom. I could have sworn I had it in there. Well, here it is anyway.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  12. #12
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location

    wow! :)

    This program looks amazing. So many things to play with and learn! I wanna call in "sick" just to be able to stay home and work with it. You have just expanded my horizons exponentially. I wish I would have become curious about it sooner.

    I figured out how to open the project and view all the parts to it. It looks very powerful. I'm excited to get into this.

    Thanks Joseph
    Office 2010, Windows 7
    goal: to learn the most efficient way

  13. #13
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location

    =)

    It is amazing

    And glad to help, Tom. It's cool to see you so excited about it.

    And thanks to you for starting this thread This is actually my first 'real' VB.NET app (aside from book examples) and I'm happy to start helping out and creating useful applications. I needed something to kick-start me




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  14. #14
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    I found some great video tutorials on how to use VB2005 Express here:
    http://msdn.microsoft.com/vstudio/ex...h/default.aspx

    I'm actually very shocked that Microsoft bothered to make such useful tutorials! There's a first time for everything.

    The guy who does those tutorials also recommends going here for over 500 tutorials on using Visual Studio: http://www.learnvisualstudio.net/Default.aspx

    Looks like I'll probably be joining them soon.
    Office 2010, Windows 7
    goal: to learn the most efficient way

  15. #15
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Nice finds, Tom

    Before you go paying for those videos, though (on the learnvisualstudio.net site) check out YouTube for some free videos.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  16. #16
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  17. #17
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by malik641
    Nice finds, Tom

    Before you go paying for those videos, though (on the learnvisualstudio.net site) check out YouTube for some free videos.
    You know what, Tom? After watching almost all those VB.NET videos in your first link. I think I might just join www.learnvisualstudio.net myself




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  18. #18
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    hehe yeah. The quality of those tutorials on youtube are terrible. I know they mean well, but I just can't learn from youtube.
    Office 2010, Windows 7
    goal: to learn the most efficient way

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •