Wednesday, November 20, 2013

How to make a household budget calculator with Excel


  • Open a new Excel workbook. 
  • Name the first worksheet Expenditures. 
  • Name the second worksheet Budget Calculations. 
  • Delete any remaining worksheets.





  • On the first worksheet, add the text Expenditures to cell A1. 
  • Merge and center A1 across three columns and two rows. 
  • Add the following text to cells A3, B3, and C3: Category, Amount, Description. 







  • Set up the Budget Calculations worksheet as follows:







  • On the Expenditures worksheet, add the following data validation to cell A4. Apply the same validation to 100 or so cells in the same column. (Click the bottom right corner of cell A4 and drag down to apply the validation to the column.) The data validation will add a dropdown list in column A of the Expenditures worksheet that is comprised of the categories from column A in the Budget Calculations worksheet. 



















  • Add the following formula to cell C4 of the Budget Calculations worksheet:
    • =SUMIF(Expenditures!$A$4:$B$151,A4,Expenditures!$B$4:$B$151)
  • The above formula adds up the expenditures for each category entered into column A of the Budget Calculations spreadsheet. Drag the formula down the column. 
  • Add categories to column A of the Budget Calculations according to the way you want to categorize expenditures. These can be changed as needed. 
  • Add the amounts you want to budget for each category to column B of the Budget Calculations. 



  • Add the following formula to cell D4 to calculate the Remaining amount: =B4 - C4
    • Drag the formula down.
  • Add totals to the bottom row to show the total Budgeted amount, the total Actual amount spent, and the total Remaining.
  • Click here to download the file: https://sites.google.com/site/benberginsfiles/Budget%20Calculator.xlsx
  • You can save the file as a template so you can create a fresh copy each month. Just go to Save As and choose Excel Template. 
    • I usually name each file after the year and month I'm tracking: 2013-12_Budget.xlsx for example. 





Saturday, November 9, 2013

How to secure your Access application using Active Directory

If your network is part of an Active Directory domain, then you can easily use domain groups as a means of securing your Microsoft Access application.

The IsMember function determines if a logged in user is a member of a specific Active Directory group or not.

Function IsMember(strDomain As String, strGroup As String, strMember As String) As Boolean
    Dim adGrp As Object
    Dim strPath As String
    
    strPath = "WinNT://" & strDomain & "/"
    Set adGrp = GetObject(strPath & strGroup & ",group")
    IsMember = adGrp.IsMember(strPath & strMember)
    
End Function

If IsMember("mydomain.com", "Accounting", Environ("Username")) Then
    ' Allow access to sensitive information
Else
    ' Deny access to sensitive information 
End If

You can use the IsMember function to show or hide forms, to show or hide buttons, to add filters to SQL code, etc. Here are some code samples of ways you might secure your application using Active Directory groups:

This code hides and disables a button when the form loads:

Private Sub Form_Load()
    If IsMember("mydomain.com", "Domain Admins", Environ("Username")) Then
        Me.cmdLoadData.Visible = True
        Me.cmdLoadData.Enabled = True
    Else
        Me.cmdLoadData.Visible = False
        Me.cmdLoadData.Enabled = False
    End If
End Sub

You can also use the IsMember function as you build a dynamic SQL Statement. The following code allows Executives to see anyone's salary information but only allows others to see their own salary information.

    If IsMember("mydomain.com", "Executives", Environ("Username")) Then
        strSQL = "SELECT * FROM SalaryInfo"
    Else
         strSQL = "SELECT * FROM SalaryInfo" _
                        & " WHERE Username = '" & Environ("Username") & "'"
    End If









Monday, November 4, 2013

How to save a Word document as a PDF using Visual Basic (VBA)

Set a reference to the Microsoft Word object library.






















Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim pdfName As String
Dim curPath as String
curPath = CurrentProject.Path  

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open(curPath & "\Templates\Some Doc.docx")

pdfName= curPath & "\Some Folder\Converted Doc.pdf"

wordDoc.ExportAsFixedFormat OutputFileName:=pdfName, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True

wordDoc.Close saveChanges:=False

How to delete files with Visual Basic (VBA)

dim folderName as String
folderName = "\\Server Name\Share Name\Some Folder\Old Files\"    

On Error Resume Next
        Kill folderName & "*.*" 
On Error GoTo 0


To delete a specific file type, replace the second "*" wildcard with the file extension of the files you want to delete:

On Error Resume Next
        Kill folderName & "*.xlsx" 
On Error GoTo 0

Be careful with the Kill function as you can inadvertently delete valuable files. Make sure you specify the folder from which you want to delete files. And it's best to specify the file type or the file name itself. Be as specific as possible with the Kill command.

VBA Functions for working with Pervasive SQL (PSQL)

The following function will set up your connection to a Pervasive SQL database. You will need to set a reference to the Microsoft Active X Data Objects library. This function creates a DSN-less connection that will allow you to execute pass through queries using raw SQL. Pass through queries require more knowledge of SQL than using linked tables and the Access query builder. But linked tables can be a hassle to deal with and create numerous maintenance headaches. And pass-through queries are more versatile and run more efficiently than linked table queries.

Public Function psqlConn(dbName as String) As ADODB.Connection

    Dim conn As New ADODB.Connection
    Dim strConn As String
    strConn = "Driver={Pervasive ODBC Client Interface}; ServerName=SomeServer.1583;ServerDSN=" & dbName & "; UID=Uname; PWD=Pass; ArrayFetchOn=1; ArrayBufferSize=8; TransportHint=TCP:SPX;"
    conn.Open strConn
    Set psqlConn = conn
 
End Function



The next function is for SQL SELECT queries. You'll use the previous function to set up the connection and pass it as the conn argument to the function. The function returns and ADODB record set. Remember to close the connection when you are done with it (conn.Close). 

Dim conn As New ADODB.Connection
Set conn = psqlconn(dbName)
dim strSQL as String

strSQL = "SELECT * FROM SomeTable WHERE id = '" & varID & "'"

Public Function psqlQuery(conn As ADODB.Connection, strSQL As String) As ADODB.Recordset

    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandTimeout = 120 ' Keep queries from timing out after the default of 30 seconds
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute
    
    Set psqlQuery = rs
    

End Function


The next function is for Insert, Update, and Delete queries. 

Public Function psqlNonQuery(conn As ADODB.Connection, strSQL As String) As Boolean

    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandTimeout = 120 ' Keep queries from timing out after the default of 30 seconds
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    cmd.Execute
    
    psqlNonQuery = True
    
End Function