locked
SQL Codes in VBA for Excel RRS feed

  • Question

  • Hi,

    I have an excel file with 3 sheets

    1st Excel sheet contains a List of Student names and Performance

    2nd Excel sheet contains a List of Instructors who monitor the student's performance

    3rd Excel sheet contains a List of Exemptions on the Students marked with Low Performance.

    (This is the given situation for my program)

    I have a couple of Questions regarding this.

    1. Can ADODB Connection be set as the Current Excel Workbook? (since the workbook name changes with date on when this excel file was run, as it has macros)

    2. Are the ADODB Codes similar with the Codes in VB6?

    1st Excel Sheet (Student List) Contains the Following Fields

    a) Student ID

    b) Student Name

    c) Student Perfomance

    2nd Excel Sheet Contains (Instructor List)

    a) Student ID

    b) Instructor ID

    c) Instructor Name

    3rd Excel Sheet Contains (Exemption List)

    a) Student ID

    b) Reason for Failed Performance

    c) Date

    I Have these codes but am not sure how I can make them work

    Dim conSQL_Con As ADODB.Connection

    Dim rstSQL_RSe As ADODB.Recordset

    Dim comSQL_Com As ADODB.Command

    Dim strSQL_Sel As String

    Set conSQL_Con = New ADODB.Connection

    Set rstSQL_RSe = New ADODB.Recordset

    Set comSQL_Com = New ADODB.Command

    Dim sSQL_Selec As String

     

    I need to select the "Instructor and Student ID" from the instructor List, get the "Student Name, Performance" from the Student List and if the Performance is Marked "Failed", I need to Check if it has been Exempted so I though doing a Select Statement like this would help.

    SELECT Instructor_List.Instructor_ID, Instructor_List.Student_ID, Student_List.Student_Performance

    FROM Instructor_List, Student_List, Exemption_List

    WHERE Student_List.Student_Performance = "FAIL"

    AND Instructor_List.Student_ID <> Exemption_List.Student_ID

     

    Thanks in Advance

    Friday, January 6, 2012 5:19 AM

Answers

  • Go here (www.connectionstrings.com) for your connection string.  I have connected to excel workbooks using their connection strings with no problems.  It has many other types of connections as well, just in case you ever need them.
    As for the name, I don't know that you can use code to find the current workbook, as you'd have to know what version of excel is running, and if they have more than one open, you're hosed.  What you can do is populate an input box with what you think the name should be, based on the current date.  Then if the file hasn't updated it for awhile (say a weekend), it can be changed.
    Your SQL statement is a little off.  for one, you need [] around the tab names in the workbook.  Second, you'll probably have to break that into two queries and Union them together, OR create a multilevel query.  I'd suggest the first.  Something like the following:
    Disclaimer: The following is not tested code.
     
    SELECT  I.Instructor_ID, I.Student_ID, S.Student_Name, S.Student_Performance
    FROM  [Instructor_List] I, [Student_List] S
    WHERE  I.Student_ID = S.Student_ID
    AND S.Student_Performance <> "FAIL"
    
    UNION
    
    SELECT  I.Instructor_ID, I.Student_ID, S.Student_Name, S.Student_Performance
    FROM  [Instructor_List] I, [Student_List] S, [Exemption_List] E
    WHERE  I.Student_ID = S.Student_ID 
    AND  NOT I.Student_ID = E.Student_ID
    AND S.Student_Performance = "FAIL"
    

    The first grabs all students that didn't fail.  The second grabs all the students that did fail, but were exempt.  The UNION combines the two results into one table.
    Off-topic:  Code is never plural.  You write code, but you use codes for video games.  :)

    Lines to Code by: Hardware can only do what it's told. That's why there are programmers. ** Just because you don't know how, doesn't mean it can't be done. ** When something doesn't work, it's usually a programming error. ** Just because you did something last time, is not a good enough reason to do it this time. ** If the user can do it, at some point they will. When they do, it's the programmer's fault. ** Keep your code modular. Someone else will have to read it at some point. ** Add comments. In six months, you won't know why you wrote the code like that.
    Friday, January 6, 2012 3:09 PM

All replies

  • Hi,

    You have posted in a forum area 'usually' intended for VB.Net questions.

     

    For help with EXCEL VBA ( Visual Basic for Applications ) read on please.   

    Please try the VBA area:>>

    http://social.msdn.microsoft.com/Forums/en/isvvba/threads

    This EXCEL area on answers :>>

    http://answers.microsoft.com/en-us/office/forum/excel

     

    and the EXCEL area on social.technet :>>

    http://social.technet.microsoft.com/Forums/en/excel/threads

     

    There is also the Excel for Developers forum:>>

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads


     

    Finally, here is an external ( independent forum ) area on VBA too:>>

    http://www.programmersheaven.com/mb/vba/Board.aspx




    Regards,

    profile for John Anthony Oliver at Stack Overflow, Q&A for professional and enthusiast programmers

    Click this link to see the NEW way of how to insert a picture into a forum post.

    Installing VB6 on Windows 7

    App Hub for Windows Phone & XBOX 360 developers.

    Friday, January 6, 2012 2:33 PM
  • Go here (www.connectionstrings.com) for your connection string.  I have connected to excel workbooks using their connection strings with no problems.  It has many other types of connections as well, just in case you ever need them.
    As for the name, I don't know that you can use code to find the current workbook, as you'd have to know what version of excel is running, and if they have more than one open, you're hosed.  What you can do is populate an input box with what you think the name should be, based on the current date.  Then if the file hasn't updated it for awhile (say a weekend), it can be changed.
    Your SQL statement is a little off.  for one, you need [] around the tab names in the workbook.  Second, you'll probably have to break that into two queries and Union them together, OR create a multilevel query.  I'd suggest the first.  Something like the following:
    Disclaimer: The following is not tested code.
     
    SELECT  I.Instructor_ID, I.Student_ID, S.Student_Name, S.Student_Performance
    FROM  [Instructor_List] I, [Student_List] S
    WHERE  I.Student_ID = S.Student_ID
    AND S.Student_Performance <> "FAIL"
    
    UNION
    
    SELECT  I.Instructor_ID, I.Student_ID, S.Student_Name, S.Student_Performance
    FROM  [Instructor_List] I, [Student_List] S, [Exemption_List] E
    WHERE  I.Student_ID = S.Student_ID 
    AND  NOT I.Student_ID = E.Student_ID
    AND S.Student_Performance = "FAIL"
    

    The first grabs all students that didn't fail.  The second grabs all the students that did fail, but were exempt.  The UNION combines the two results into one table.
    Off-topic:  Code is never plural.  You write code, but you use codes for video games.  :)

    Lines to Code by: Hardware can only do what it's told. That's why there are programmers. ** Just because you don't know how, doesn't mean it can't be done. ** When something doesn't work, it's usually a programming error. ** Just because you did something last time, is not a good enough reason to do it this time. ** If the user can do it, at some point they will. When they do, it's the programmer's fault. ** Keep your code modular. Someone else will have to read it at some point. ** Add comments. In six months, you won't know why you wrote the code like that.
    Friday, January 6, 2012 3:09 PM
  • Hi drill_master,

    Welcome to the MSDN forum.

    This queue is about Visual Basic. I’m afraid that your topic about VBA is unsuitable here. For better support, I will move this thread to Microsoft Visual Basic for Applications (VBA) Forum.

    Sorry for any inconvenience and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 9, 2012 3:35 AM
  • Hi Mark,

     

    Sorry for this


    ***novice***
    Wednesday, January 11, 2012 9:37 AM
  • Hi John,

     

    My Appologies


    ***novice***
    Wednesday, January 11, 2012 9:38 AM
  • Hi drill_master,

    How about the problem on your side?

     

    Do you still need any assistant about the problem? If you still show any concern on the problem, just feel free to let us know.

     
    Best Regards,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 27, 2012 10:19 PM