Accessing a local database via JavaScript

WARNING: This is NOT a secure method of accessing a database. The code below is strictly for demonstration purposes. Using it in a production environment is strongly discouraged.

Having said that, there are limited cases where the following can prove useful. In this case, creating an HTML Application file (HTA), or a local HTML file to access local data in an MS Access database.

The reason for choosing this method over other options such as designing a VBA macro, or more complex solutions requiring a server, is because it is simple, very lightweight, and allows for considerably more versatility in UI design than a similar solution in VBA would provide.

function getScalarValueFromDB() {
    var connection = new ActiveXObject("ADODB.Connection");
    var connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\MyDatabaseFolder\\MyAccessDatabase.mdb;";
    connection.Open(connectionstring);

    var rs = new ActiveXObject("ADODB.Recordset");
    rs.Open("SELECT ColumnOne FROM MainTable;", connection);
    alert(rs.Fields("ColumnOne"));
    
    rs.close();
    connection.close();
}

The JavaScript function above retrieves a value from one column in a fictitious Microsoft Access Database and displays it using the alert() function.

This code can only work in Internet Explorer (tested on version 11). To run it, simply place it inside <script> tags like you would any other JavaScript code.

VBA: Export all modules in project

Option Explicit

Sub ExportAllModules()

    Dim vbaProject As VBProject
    Dim vbaComponent As VBComponent
    
    Set vbaProject = Application.VBE.ActiveVBProject
    
    For Each vbaComponent In vbaProject.VBComponents
        If vbaComponent.Type = vbext_ct_StdModule Or vbaComponent.Type = vbext_ct_MSForm Or vbaComponent.Type = vbext_ct_ClassModule Or vbaComponent.Type = vbext_ct_Document Then
            vbaComponent.Export "<LOCATION TO SAVE FILES>" & vbaComponent.Name & ".vb"
        End If
    Next
        
End Sub

VBA: Correct Date Formatting in Excel

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''         Designed by Luc Schulz
'''         March 27, 2018
'''
'''         The purpose of this module is to fix incorrectly
'''         formatted dates in Excel Spreadsheets.
'''
'''         To use this tool, select the dates with the wrong
'''         formatting, then run the CorrectSelectedDates() Sub.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Sub CorrectSelectedDates()

    Dim rng As Range
    Set rng = Selection
    
    Dim c As Variant
    For Each c In rng
        
        Dim dt As String
        dt = Trim(c.Value)
        
        If IsDateFormattedBadly(dt) = True Then
            c.NumberFormat = "yyyy-mm-dd;@"
            c.Value = FixIncorrectDateFormat(dt)
        End If
    Next

End Sub

' RETURNS TRUE IF THE DATE IS FORMATTED AS MM/DD/YYYY OR AS DD/MM/YYYY
Private Function IsDateFormattedBadly(cellValue As String) As Boolean
        
    If InStr(cellValue, "/") = 3 And InStr(5, cellValue, "/") = 6 Then
        IsDateFormattedBadly = True
    End If
    
End Function

'SEPARATES THE DATE STRING INTO YEAR, MONTH AND DAY THEN CONVERTS IT INTO A DATESERIAL VALUE
Private Function FixIncorrectDateFormat(cellValue As String) As Date
    
    Dim y As String, m As String, d As String
    
    y = Right(cellValue, 4)
    m = Left(cellValue, 2)
    d = Mid(cellValue, 4, 2)
    
    If CInt(m) <= 12 Then
        FixIncorrectDateFormat = DateSerial(y, m, d)
    Else
        FixIncorrectDateFormat = DateSerial(y, d, m)
    End If
    
End Function