The following Excel VBA macro will prompt the user for the Source Column, then prompt the user for the destination column, run the query for all rows and return the employees active status in the destination column. It utilizes ADO to retrieve the results from an Oracle Database. (Note: there are more efficiency tweaks that could be applied to this function to speed up the retrieval via ADO, however the function in its current form will suffice for 99 percent of all queries I use. I will leave these up to the reader to discover more optimized methods).
Sub get_ActiveStatus()
'CONSTS-----------------------------------------------------------
'Replace with sql statement to use. Be sure the return columns is called "Status" for this query
Const SQL_TO_USE As String = "SELECT 'X' Status FROM employees WHERE cd_emp_type <> 'X' AND no_emp = ?"
'Define the row to start on to take into account header rows
Const START_ROW As Integer = 2
'The database connection string, change for each database used
Const DATABASE_CONNECTION_STRING As String = "Provider=MSDAORA.1;Password=cxxxxxx;User ID=cxxx;Data Source=ts;Persist Security Info=True"
'VARIABLES--------------------------------------------------------
'x is a generic counter, then there are the 3 objects used in ADO
Dim x As Integer
Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Dim rec As ADODB.Recordset
'Source and destination columns
Dim srcColumn As String
Dim dstColumn As String
'Variable to store the number of rows to process
Dim LastRow As Integer
'CODE-------------------------------------------------------------
'Get the number of rows in this worksheet by using Excels internal countA function
'I use this instead of looking for blank cells because this has shown itself to be more accurate
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
'If there are no rows, exit out of the function
If LastRow = 0 Then
MsgBox "There are no rows in this worksheet", vbOKOnly
Exit Sub
End If
'Set up the connections using the database string defined above
con.ConnectionString = DATABASE_CONNECTION_STRING
con.CursorLocation = adUseClient
con.Mode = adModeRead
con.Open
'Set up our command object to do a prepared query using out constant defined above
com.CommandType = adCmdText
com.Prepared = True
com.CommandText = SQL_TO_USE
com.Parameters.Append com.CreateParameter("EmpID", adVarChar, adParamInput, 50)
Set com.ActiveConnection = con
'Prompt the user for the source column containint employee IDs and the destination
'column indicating that the employee is active
srcColumn = InputBox("Enter the Source Column: ")
dstColumn = InputBox("Enter the Destination Column: ")
'Set our generic counter to the start row defined in the constants section
x = START_ROW
'Loop from our starting row to the last row, on error use the general exception handler
On Error GoTo Error_Occured
For x = START_ROW To LastRow
'set the employee ID to look up as the source column and row and execute query
com("EmpID") = Trim(Range(Trim(srcColumn) & Trim(Str(x))).Value)
Set rec = com.Execute
'If there are records returned, set the destination to the returned result
If (rec.RecordCount > 0) Then
Range(Trim(dstColumn) & Trim(Str(x))).Value = rec("Status")
End If
'Set the recordset to nothing
Set rec = Nothing
'Do any background events, such as processing keypresses or switch windows
DoEvents
Next
'Close and clear all objects
con.Close
Set com = Nothing
Set con = Nothing
Exit Sub
'The general exception handler
Error_Occured:
'close the database connection and clear objects
con.Close
Set com = Nothing
Set con = Nothing
'Let the user know there is a problem
MsgBox "An error occured with processing the macro", vbCritical
End Sub
What I would like to do now is have these macros available to me anytime I need them, however I do not want them to appear in Excel files I send out. So I will set up a personal file that will open each time I open Excel. These steps apply to Excel 2000.
First, open up Excel and create a new worksheet.
In your new sheet, delete the tabs for “Sheet 2” and “Sheet 3”.
Then go up to Window, and choose Hide.
Then Go to Tools, Macros, and Visual Basic Editor.
In the Visual Basic Editor, you can see that your new Worksheet is still available for Macro editing. We will create a new Module to house our Macros. So, right mouse click on the name of your worksheet, and choose Insert, and Module.
Under the property editor, name your Module whatever name you feel is appropriate. I have chosen “ExcelMacros” here.
Now paste the Macro into the modules code window. Since this Macro makes use of the Microsoft ActiveX data object, we need to add it as a reference. To do this, Tools, References, and select Microsoft ActiveX Data Objects from the list of libraries.
Once completed, choose File and Save. You will get a File Explorer dialog. Here, navigate to my user profiles section for Excel, which is at “C:\Documents and Settings\jward\ Application Data\Microsoft\Excel\XLSTART\” and I will save the file as “PersonalMacros.XLS”.
Once I exit out of Excel and reenter, I go back to the VBA editor, and I see that my personal macros is still there. The only drawback to this approach is that when I open Excel, I do not get a blank worksheet, which is a sacrifice I am willing to make.
I am lazy by nature, so I do not want to have to open up the Macro editor each time I want to run this script, so I will create a button on a toolbar for easy access. First, go to File and New to create a blank Worksheet. We won’t do anything with this worksheet, however Excel will not allow us to assign a Macro to a button unless a Worksheet is open and visible.
First, I right mouse click in the tool bar area and choose customize.
I then choose the toolbar tab, click the New button, and enter the name of my new Toolbar as “Personal Macros”.
By default, the new toolbars are already visible. With the Customize dialog box still open, I go over to the Commands tab, scroll down to under the Categories list box to Macros, and drag the Custom Menu Item over to my new toolbar.
I right mouse click on the toolbars “Custom Menu Item” to get the properties dialog, and I change the name to “Get Active Employee Status”.
I then right mouse click again, and choose “Assign Macro”.
When the dialog opens up, I can now choose the macro I created.
And that’s it. Now I can see this macro in action. I open up a document with Employee ID’s to test. I click on my button and am greeted with my dialog box asking for the Source Column.
Another dialog box prompting me for the destination column pops up, which I fill in. Once I hit OK, I can watch as the macro fills in my values for me.
You can see where this is a time saver. Almost all objects in Microsoft Office, and a lot in Windows are accessible through VBA. This is one of the reasons that Visual Basic is such a popular language. However, in some cases, it allows a little too much power for an office suite. In fact, I’ve seen things as far out as Port Scanners written in VBA. This is also one of the reasons that Microsoft receives so much criticism for VBA inclusion in so many Microsoft products. Many say this is a vendor lock in tactic, however I take it for what it is, a time saver in many cases. I have dozens of simple macros based off this one that allow me to very easily meet customers request for information at the click of a button. Incidentally, OpenOffice Calc program has in very recent versions incorporated a similar Macro language that I intend to look into. While not as easy to develop in as VBA, I am sure it is every bit just as powerful, if not more.
Posts
No comments:
Post a Comment