February 2nd, 2012
I had a need to create folders from Access, but needed a means to first validate that the folders names were acceptable as Windows does not allow certain characters and has certain basic rules (refer to the 2 links commented out in the function below for all the details). As such, I created the following simple function which I supply the folder name to and it returns True/False whether the string is acceptable or not. It really wasn’t very difficult and this is the perfect situation in which to utilize the power of regular expression to validate the folder name with!
'---------------------------------------------------------------------------------------
' Procedure : IsInvalidFolderName
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Validates whether the string passed is an acceptable folder name
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolderName name of the folder you're wanting to create
'
' Usage:
' ~~~~~~
' IsValidFolderName("MsAccess Databases") will return True
' IsValidFolderName("MsAccess :: Databases") will return False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-Feb-01 Initial Release
'---------------------------------------------------------------------------------------
Function IsValidFolderName(ByVal sFolderName As String) As Boolean
'http://msdn.microsoft.com/en-us/library/windows/desktop/aa365247(v=vs.85).aspx#file_and_directory_names
'http://msdn.microsoft.com/en-us/library/ie/ms974570.aspx
On Error GoTo Error_Handler
Dim oRegEx As Object
'Check to see if any illegal characters have been used
Set oRegEx = CreateObject("vbscript.regexp")
oRegEx.Pattern = "[<>:""/\\\|\?\*]"
IsValidFolderName = Not oRegEx.test(sFolderName)
'Ensure the folder name does end with a . or a blank space
If Right(sFolderName, 1) = "." Then IsValidFolderName = False
If Right(sFolderName, 1) = " " Then IsValidFolderName = False
Error_Handler_Exit:
On Error Resume Next
Set oRegEx = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & vbCrLf & _
"Error Source: IsInvalidFolderName" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End FunctionEnjoy!
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | 
No Comments »
September 16th, 2011
It can often come handy to be able to quick determine if a Folder/Directory exists or not. Below is a function I created some time ago to do exactly that.
'---------------------------------------------------------------------------------------
' Procedure : FolderExist
' DateTime : 2009-Oct-02 13:51
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Test for the existance of a Folder/Directory
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolder - Full path of the folder to be tested for
'---------------------------------------------------------------------------------------
Function FolderExist(sFolder As String) As Boolean
On Error GoTo Error_Handler
If sFolder = vbnullsring Then GoTo Error_Handler_Exit
If Dir(sFolder, vbDirectory) <> vbNullString Then
FolderExist = True
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
If Err.Number <> 52 Then
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: FolderExist" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured
End If
Resume Error_Handler_Exit
End Function
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
September 15th, 2011
One of he most common methods for creating directories/folders in VBA is to use the MkDir statement. For instance:
One quickly learns the limitations of this technique the minute they have to create a directory structure with multiple sub-folders. MkDir can only create 1 directory at a time and cannot create a sub-directory. Hence, assuming that C:\databases does not already exist, the following would not work and will return an error!
MkDir "C:\databases\msaccess\"
If you absolutely want to create such a structure using the MkDir statement you’d have to do so using 2 MkDir statement. For instance:
MkDir "C:\databases\"
MkDir "C:\databases\msaccess\"
Now if you need to merely create 1 or 2 sub-folder MkDir may still be acceptable, but there are cases where this is simply impracticle and another solution needs to be found. Well, I found two possible solutions!
One possible approach can be found at Creating Nested Directories.
The second I found searching through the net and I no longer know the original source of the code (if someone knows e-mail me and I will put credit where it is due). It is a simple API which can create multiple directories in 1 call.
Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
Public Sub MakeFullDir(strPath As String)
If Right(strPath, 1) <> "\" Then strPath = strPath & "\" 'Optional depending upon intent
MakeSureDirectoryPathExists strPath
End SubAnother possible solution, if someone wanted to tinker a little would be to merely parse the path into it’s directories and the using the DIR statement evaluate and create the directories where need be. Shouldn’t be that hard, but I haven’t taken the time to do this (maybe one day I will and will update this post then).
Okay, so it bothered me and I had to quickly put something together to stop my brain from churning! Below is what I pieced together rapidly. It is missing proper variable definitions (DIM statements) and error handling, but from my very brief testing, it does appear to work and doesn’t require any APIs! 100% VBA.
Public Sub MyMkDir(sPath As String)
Dim iStart As Integer
Dim aDirs As Variant
Dim sCurDir As String
Dim i As Integer
If sPath <> "" Then
aDirs = Split(sPath, "\")
If Left(sPath, 2) = "\\" Then
iStart = 3
Else
iStart = 1
End If
sCurDir = Left(sPath, InStr(iStart, sPath, "\"))
For i = iStart To UBound(aDirs)
sCurDir = sCurDir & aDirs(i) & "\"
If Dir(sCurDir, vbDirectory) = vbNullString Then
MkDir sCurDir
End If
Next i
End If
End SubAs you can see, there are numerous way to handles this issue. Hopefully this helped answer a question for a few of you out there!
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
August 17th, 2011
I recently helped an individual in an Access Forum who wanted to know how to open a password protected Excel workbook/spreadsheet. Although the question was Access specific, the code can easily be used in Word, PowerPoint,…
'---------------------------------------------------------------------------------------
' Procedure : OpenPwdXLS
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Open a password protected Excel Workbook
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strWrkBk : Full path and Filename of the Excel Workbook to open
' sPwd : Password to unlock/open the Workbook in question
'
' Usage:
' ~~~~~~
' OpenPwdXLS "C:\Testing\book1.xls", "MyPassword"
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Jun-11 Initial Release
'---------------------------------------------------------------------------------------
Function OpenPwdXLS(strWrkBk As String, sPwd As String)
'Use late binding so no reference libraries are required
On Error GoTo Error_Handler
Dim xlApp As Object
Dim xlWrkBk As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set xlApp = CreateObject("excel.application")
Else
On Error GoTo Error_Handler
End If
xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open(strWrkBk, , , , sPwd)
'... the rest of your code goes here
Error_Handler_Exit:
On Error Resume Next
Set xlWrkBk = Nothing
Set xlApp = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: OpenPwdXLS" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
MS Access - Excel Automation, MS Access VBA Programming, MS Word VBA Programming | No Comments »
June 22nd, 2011
I ran into a particular problem with an associate of mine not too long ago and thaught I’d share the problem & solution with everyone. He had been given an .accdb database that evidently came from an mdb originally as it contained a custom command bar. The problem being that 2007/2010 support such command bars by simply placing them in the Add-Ins tab. However, this issue lies with the fact that 2007/2010 no longer offer the general user/developer a method to edit such command bars as they are considered to be deprecated. My associate wanted to merely delete this command bar and replace it with a proper custom Ribbon Tab. So how do you delete a command bar? Good question! In 2007/2010 the only solution is to use VBA. The actual code to delete a command bar is very simple, as shown below.
Application.CommandBars("CommandBarName").DeleteSound simple you say. Sadly, no! Once again because MS has decided no longer provide any tools to work with these ‘elements’, you cannot identify the name of the Add-Ins toolbars. if you can’t identify it, you can’t delete it! So what to do? Well, I came up with another simple solution and created a procedure that merely listed all the command bars within the current database. then we could go through the list and identify the one that we needed to delete. Below are two slightly different procedures. The first one is a more general version which merely lists all of the command bars within the database, the second is a slightly more refined version that only lists open command bars. Since the Add-Ins Tab was active, the 2nd procedure was a better option in our case and thus reduce the list we had to sift through (our list went from 202 items to 4 – from there it was easy to determine which one we needed to eliminate).
'---------------------------------------------------------------------------------------
' Procedure : ListCmdBars
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Lists all the command bars within the current database
' Compatibility: Works with MS Access, Word, Excel, PowerPoint, ...
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-June-22 Initial Release
'---------------------------------------------------------------------------------------
Function ListCmdBars()
On Error GoTo Error_Handler
Dim i As Long
Dim sCmdBar As CommandBar
Debug.Print "Number", "Name", "Visible", "Built-in"
For i = 1 To Application.CommandBars.Count
Set sCmdBar = Application.CommandBars(i)
Debug.Print i, sCmdBar.Name, sCmdBar.Visible, sCmdBar.BuiltIn
Next i
Error_Handler_Exit:
On Error Resume Next
Set sCmdBar = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ListCmdBars" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : ListVisibleCmdBars
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Lists all the visible command bars within the current database
' Compatibility: Works with MS Access, Word, Excel, PowerPoint, ...
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-June-22 Initial Release
'---------------------------------------------------------------------------------------
Function ListVisibleCmdBars()
On Error GoTo Error_Handler
Dim i As Long
Dim j As Long
Dim sCmdBar As CommandBar
Debug.Print "Number", "Name", "Visible", "Built-in"
For i = 1 To Application.CommandBars.Count
Set sCmdBar = Application.CommandBars(i)
If sCmdBar.Visible = True Then
j = j + 1
Debug.Print j, sCmdBar.Name, sCmdBar.Visible, sCmdBar.BuiltIn
End If
Next i
Error_Handler_Exit:
On Error Resume Next
Set sCmdBar = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ListVisibleCmdBars" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End FunctionSo as you can see, it is still possible to manipulate command bars in MS Access 2007/2010, but it can now only be done through the use of VBA code!
Thanks to a post by Albert Kallal, a fellow MVP, we also found out that it could be necessary to execute the following
CurrentDb.Properties.Delete("StartUpMenuBar") and in our case it was required as even after deleting the commandbar in question we were still receiving the error message
… can’t find the object ‘MyCommandBarName.’
If ‘MyCommandBarName’ is a new macro or macro group, make sure you have saved it and that you have type its name correctly
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
June 8th, 2011
You can use the following procedure to extract the path from a full file name. You supply the file address, complete file path and file name (ie: “C:\Documents and Settings\User\Desktop\Details.txt”) and it will return the file name (ie: “Details.txt”)
'---------------------------------------------------------------------------------------
' Procedure : GetFileName
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return the filename from a path\filename input
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - string of a path and filename (ie: "c:\temp\test.xls")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Feb-06 Initial Release
'---------------------------------------------------------------------------------------
Function GetFileName(sFile As String)
On Error GoTo Err_Handler
GetFileName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetFileName" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
GoTo Exit_Err_Handler
End Function
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
June 7th, 2011
You can use the following procedure to extract the path from a full file name. You supply the file address, complete file path and file name (ie: “C:\Documents and Settings\User\Desktop\Details.txt”) and it will return the path (ie: “C:\Documents and Settings\User\Desktop\”)
'---------------------------------------------------------------------------------------
' Procedure : GetFilePath
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return the path from a path\filename input
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - string of a path and filename (ie: "c:\temp\test.xls")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Feb-06 Initial Release
'---------------------------------------------------------------------------------------
Function GetFilePath(sFile As String)
On Error GoTo Err_Handler
GetFilePath = Left(sFile, InStrRev(sFile, "\"))
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetFilePath" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
GoTo Exit_Err_Handler
End Function
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
June 6th, 2011
Ever simply wanted to append data into an existing text file? The procedure below does exactly that. Simply supply the full path and file name of the text file to append to, and supply the string to append and voila!
'---------------------------------------------------------------------------------------
' Procedure : AppendTxt
' DateTime : 2007-Mar-06 10:14
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Output Data to an external file (*.txt or other format)
' ***Do not forget about access' DoCmd.OutputTo Method for
' exporting objects (queries, report,...)***
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - name of the file that the text is to be output to including the full path
' sText - text to be output to the file
'---------------------------------------------------------------------------------------
Function AppendTxt(sFile As String, sText As String)
On Error GoTo Err_Handler
Dim FileNumber As Integer
FileNumber = FreeFile ' Get unused file number
Open sFile For Append As #FileNumber ' Connect to the file
Print #FileNumber, sText ' Append our string
Close #FileNumber ' Close the file
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: AppendTxt" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
GoTo Exit_Err_Handler
End Function
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
June 5th, 2011
Here is another simple procedure that allows one to verify/check if a file exists or not.
'---------------------------------------------------------------------------------------
' Procedure : FileExist
' DateTime : 2007-Mar-06 13:51
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Test for the existance of a file; Returns True/False
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFile - name of the file to be tested for including full path
'---------------------------------------------------------------------------------------
Function FileExist(strFile As String) As Boolean
On Error GoTo Err_Handler
FileExist = False
If Len(Dir(strFile)) > 0 Then
FileExist = True
End If
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: FileExist" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
GoTo Exit_Err_Handler
End Function
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
June 4th, 2011
It still amazes me how Microsoft can develop these complexe applications but seems to overlook some simple functions that they should included within them to aid developers… But then, as demonstrated with the release of Office 2007 and 2010, Microsoft is not interested in the developer, they are only interested in the end-user’s opinion. Not productivity (that went down, about 30-40% drop in efficiency, the tubes with their change of format)! So all that matters is looks, the feel – very superficial (rant over)!!!
This will be the first in a series of procedure that I will be posting in the coming months in which I hope to demonstrate how you can use the ‘Microsoft Visual Basic for Application Extensibility’ library in conjuntion with the power of VBA to learn more, control more, manipulate more the VBE.
In this first post, I simply wanted to create a simple procedure that would give me a breakdown of my Access project. I wanted to return a listing of procedure per module with a line count. As you can see, the ‘Microsoft Visual Basic for Application Extensibility’ enable us to perform this task with ease with little code. Heck, half of the code below is to write to the generated text file!
'---------------------------------------------------------------------------------------
' Procedure : GetVBEDeatils
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Goes throught the VBE and creates a text file which give a brief listing
' of the procedures within each module and a line count for each
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
' Requirements: reference to the Microsoft Visual Basic for Application Extensibility
' library.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-June-04 Initial Release
'---------------------------------------------------------------------------------------
Function GetVBEDeatils()
Dim vbProj As VBProject
Dim vbComp As VBComponent
Dim vbMod As CodeModule
Dim sProcName As String
Dim pk As vbext_ProcKind
Dim FileNumber As Integer
Dim strFile As String
Const vbNormalFocus = 1
'Where do youwant the text file created
strFile = "C:\VBEDetails.txt"
If Len(Dir(strFile)) > 0 Then Kill strFile
FileNumber = FreeFile 'Get unused file number.
Open strFile For Append As #FileNumber 'Create file name.
For Each vbProj In Application.VBE.VBProjects 'Loop through each project
Print #FileNumber, vbProj.Name
For Each vbComp In vbProj.VBComponents 'Loop through each module
Set vbMod = vbComp.CodeModule
Print #FileNumber, " " & vbComp.Name & " :: " & vbMod.CountOfLines & " total lines"
Print #FileNumber, " " & String(80, "*")
iCounter = 1
Do While iCounter < vbMod.CountOfLines 'Loop through each procedure
sProcName = vbMod.ProcOfLine(iCounter, pk)
If sProcName <> "" Then
Print #FileNumber, " " & sProcName & " :: " & vbMod.ProcCountLines(sProcName, pk) & " lines"
iCounter = iCounter + vbMod.ProcCountLines(sProcName, pk)
Else
iCounter = iCounter + 1
End If
Loop
Print #FileNumber, ""
Next vbComp
Next vbProj
Close #FileNumber 'Close file.
Set vbMod = Nothing
'Open the generated text file
Shell "cmd /c """ & strFile & """", vbNormalFocus
End Function
MS Access, MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | 1 Comment »
March 13th, 2011
What I wouldn’t have given to be aware of this add-in when I was starting out as a developer! Seriously, this add-in would have saved me hundreds, if not thousands, of hours searching online, posting to forums, to find out how to code what I needed to do. Beyond which, it provides the user with a standardized set of procedures, instead of trying to piece together countless routines found here and there as you search online.
Just yesterday, I needed a particular routine and instead of searching online, as I always have done until now, I opened the TVSB, performed a quick search, exported the appropriate code into my module and was back at work in a matter of 1-2 minutes, if that!
So what did I think of the TVSB?
Cons:
- I wish it could be somehow directly integrated within the VBE as done with certain other add-ins rather than a separate popup application. Have some type of integrated toolbar with a drop down category/procedures/… select the procedure and BAM there is. AND, I’m not saying it is hard to export the procedures from the SourceBook the way it is currently setup. That said, even though it would be nice, I myself am not sure how it could be accomplished.
- My other issue is that their code uses Early biding which I try to avoid normally as it can causes reference issues. So their code is a nice starting point, but I would convert most of the classes, procedures,… into late binding for my own purposes. Over the course of several years, I have learnt that Late Binding avoids reference issues and this outways (in my opinion) any performance benefits Early Binding presents. At the end of the day, each developer has their own opinion and experience with regards to this aspect of programming, so feel free to make up your mind on this aspect yourself. To learn a little bit more about the pros and cons of Early Binding vs. Late Binding take a look at Early vs. Late Binding
from the Word MVP site, it is a short overview of the issue.
Pros:
- Easy to install
- Can be integrated to work in a team environment (untested)
- Easy to navigate and work with
- It is very intuitive
- Came with a user manual! Just this to me put this application above most others!!! Although, in this case, a manual is not necessary.
- The code itself, is well categorized so you can find things quite easily just by noising around.
- Effective search tool enable one to quickly search through the repository.
- Extendable. You can add your own code (procedures, modules, …) to the repository so you can build upon what is already there.
- Good export utility (export directly into your module, to a file, …)
- Customizable – You can configure the Error Handler and other elements to suit your programming methodologies.
Put simply, the FMS Total Visual SourceBook (TVSB) is a simple to use, powerful work tool that can easily accelerate the development process of any developer and give you a leg up on your competition.
MS Access General Information, MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming, Product Reviews | No Comments »
January 6th, 2011
One thing any good worker will tell you is that you must have the right tools to do the job. Computer programming, MS Access database development, is no exception to this rule! I thought I’d list a few add-ins, plug-ins, etc. that I have come across, or heard of. This is an unbiased listing and I have no link to any of the vendors, nor have I even used all of them. I am simply trying to regroup them to help you find them, and you can judge their usefullness on your own. In a future post, I will examine the 2 or 3 that I use myself and find very useful.
Free Ones
MZ-Tools
Smart Indenter
V-Tools
Ones you have to pay for
Find and Replace (30 day evaluation then you have to register/pay)
FMS Inc. Tools — FMS offers a multitude of various tools worth reviewing, including: Total Visual Source Book and Total Access Admin
A Few More Untested Add-ins (Update 2011-Mar-09)
Various utilies by Bill Mosca (Access MVP)
ACCESS Dependency Checker
http://www.4tops.com/query_tree.htm
http://www.4tops.com/ms_access_vba.htm
Compare Em – Compare 2 database to identify the differences and generate the necessary code to make the updates.
MS Access General Information, MS Access VBA Programming, MS Excel VBA Programming, MS Office, MS Word VBA Programming | No Comments »
December 6th, 2010
One option is to systematically check for the various dos and don’t using a series of if statements such as that in the function below:
Public Function isValidEmail(inEmailAddress As String) As Boolean
' Author: Unknown
If (Len(inEmailAddress) = 0) Then
MsgBox "Please enter your email address."
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, "@") = 0) Then
MsgBox "The '@' is missing from your e-mail address."
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, ".") = 0) Then
MsgBox "The '.' is missing from your e-mail address."
isValidEmail = False
Exit Function
End If
If (InStr(inEmailAddress, "@.") > 0) Then
MsgBox "There is nothing between '@' and '.'"
isValidEmail = False
Exit Function
End If
If ((InStr(inEmailAddress, ".")) = ((Len(inEmailAddress)))) Then
MsgBox "There has to be something after the '.'"
isValidEmail = False
Exit Function
End If
If ((Len(inEmailAddress)) < (InStr(inEmailAddress, ".") + 2)) Then
MsgBox "There should be two letters after the '.'"
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, "@") = 1) Then
MsgBox "You have to have something before the '@'"
isValidEmail = False
Exit Function
End If
isValidEmail = True
End FunctionA Second better approach, like with many web programming languages, is to use regular expressions to validate certain type of strings. Now Access’ VBA does not allow RegEx persey, but you can very easily access such functionality by adding 2 simple lines of code. You then end up with a total of a three lines of code to validate almost any string, including an e-mail address. For all the details, and a link to where you can get a multitude of the RegEx so you don’t have to reinvent the wheel simply check out my post VBA – Using Regular Expressions (RegEx)
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
October 7th, 2010
Before delving into actual programming functions and sub-routine, we must first establish an error handler. An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developper describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user. Below is a typical example of an error handler I use. Modify it in any way to suit your exact needs.
On Error GoTo Error_Handler
'Your code will go here
Error_Handler_Exit:
On Error Resume Next
Exit {PROCEDURE_TYPE}
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_ExitA Concrete Example
Sub HelloWorld()
On Error GoTo Error_Handler
MsgBox "Hello Word!"
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourModuleName/HelloWorld" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End SubNote: Although VBA provides programmers with the possibility of using the err.source statement, it sadly does not help truly identify the culprit of the current error. This is why you must manually enter in the {MODULE_NAME} / {PROCEDURE_NAME} for each error handler. Trust me, although it may take a few extra seconds to do, it will same you loads of troubleshooting time later on (I’m talking from experience)!!!
Also, if you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into the Mz-Tools add-in (free with no strings attached) in conjunction with implementing Allen Browne Error Log (for database developers). These two tools/approaches will greatly simplify and standardize your work!!!
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
September 22nd, 2010
Ever needed to run, execute a file? It could be a *.bat, *.vbs, *.exe, … One common method is to use the shell function. However, we see quite often in the forums and newsgroups regarding issues with it not working and users not getting the synthax right because of spaces in the file paths, etc.
Below is, yet again, a very simple procedure which encapsulates the file to be run/executed with the necessary quotations so that you no longer have any headaches trying to get your Shell function operational. Simply copy the function to a module and call it as indicated below.
'---------------------------------------------------------------------------------------
' Procedure : RunFile
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Run/Execute files from vba (bat, vbs,…)
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFile - full path including filename and extension
' strWndStyle - style of the window in which the program is to be run
' value can be vbHide,vbNormalFocus,vbMinimizedFocus
' vbMaximizedFocus,vbNormalNoFocus or vbMinimizedNoFocus
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' RunFile("c:\test.bat", vbNormalFocus)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-Feb-05 Initial Release
'---------------------------------------------------------------------------------------
Function RunFile(strFile As String, strWndStyle As String)
On Error GoTo Error_Handler
Shell "cmd /k """ & strFile & """", strWndStyle
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: RunFile" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End FunctionIf however you are interested in opening a file, any type of file, then please refer to my MS Access VBA – Open a File post.
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
September 17th, 2010
Have you ever needed to fill-in or update the form fields of a Word Document from say Access, Excel, … Below is a simple example of how you can do this. In it I demonstrate how to populate a textbox, as well as show you how you can check/uncheck a checkbox.
'---------------------------------------------------------------------------------------
' Procedure : UpdateDoc
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Update/Fill-in a Word document's form fields
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-Sep-17 Initial Release
'---------------------------------------------------------------------------------------
Sub UpdateDoc()
'Requires a reference to the Word object library
Dim oApp As Word.Application
Dim oDoc As Word.Document
Dim sDocName As String
On Error Resume Next
Set oApp = GetObject(, "Word.Application") 'See if word is already running
If Err.Number <> 0 Then 'Word isn't running so start it
Set oApp = CreateObject("Word.Application")
End If
On Error GoTo Error_Handler_Exit
sDocName = "YourWordDocToOpenFullPathAndExtension"
Set oDoc = oApp.Documents.Open(sDocName)
oApp.Visible = True
oDoc.FormFields("TextboxName").Result = "NewValue" 'Textbox
oDoc.FormFields("CheckboxName").CheckBox.Value = True 'Checkbox
Error_Handler_Exit:
On Error Resume Next
oDoc.Close True
oApp.Quit
Set oDoc = Nothing
Set oApp = Nothing
Exit Sub
Error_Handler:
If Err.Number = 5174 Then
MsgBox "The specified file '" & sDocName & "' could not be found.", _
vbCritical
Else
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: UpdateDoc" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Sub
MS Word VBA Programming | No Comments »
September 3rd, 2010
The following code will print out a word document.
'---------------------------------------------------------------------------------------
' Procedure : PrintDoc
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Print a Word Document
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strDoc - The path and filename of the document to be printes
' intCopies - The number of copies to be printed
'
' Usage:
' ~~~~~~~~~~~~~~~~
' PrintDoc("c:\management\evaluation.doc",1)
'---------------------------------------------------------------------------------------
Function PrintDoc(strDoc As String, intCopies As Integer)
Dim WordObj As Object
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open strDoc
WordObj.PrintOut Background:=False, Copies:=intCopies
WordObj.Documents.Close SaveChanges:=wdDoNotSaveChanges
WordObj.Quit
Set WordObj = Nothing
End Function
MS Access - Word Automation, MS Word VBA Programming | No Comments »
September 3rd, 2010
The following procedure will launch MS Word. The beauty is it uses late binding so you do not need to use reference libraries and as such avoid/minimize versioning issues. This same procedure can easily be modified to launch just about any MS Office application by simply changing the “Word.Application” portions of the code to correspond with the application you are trying to automate.
A few other strings used for common MS Office application are:
- Excel – “Excel.Application”
- Access – “Access.Application”
- Publisher – “Publisher.Application”
- PowerPoint – “Powerpoint.Application”
Sub LaunchWord()
Dim objApp As Object
'See if Word is already running
On Error Resume Next
Set objApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'Launch a new instance of Word
Err.Clear
On Error GoTo Error_Handler
Set objApp = CreateObject("Word.Application")
objApp.Visible = True 'Make the application visible to the user (if wanted)
End If
Exit Sub
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LaunchWord" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Sub
End Sub
MS Access - Word Automation, MS Word VBA Programming | No Comments »
