Periodically, the same or similar question appears in our Access Forum: How can I use Excel Functions within Access? For this reason, I decided to make this Subject TheScripts Tip of the Week. In order to demonstrate the use of Excel Functions within the context of Access, I performed the following steps in sequence:
Enough of the Overview already! I'll now display the relevant code and subsequent output. The code is well documented, but if you need any clarification on any aspect of this Tip, please feel free to ask.
- Created a Public Function called fStripNonPrinta bleCharacters() which will encapsulate the logic for executing the Excel Function. This Access Function will accept a single Argument (STRING) upon which the Excel Function will operate. The Excel Function chosen will be introduced shortly.
- Create an Instance of Excel and assign it to an Object Variable.
- Execute the Excel Function via the Object Variable with the single Argument passed to the Access Function. This Argument will consist of a String Value that is predefined.
- Set the return value of the Excel Function to the return value of the Access Function.
- Destroy the Instance of Excel as referenced by the Object Variable, then set the Variable to Nothing in order to release all resources assigned to it.
Enough of the Overview already! I'll now display the relevant code and subsequent output. The code is well documented, but if you need any clarification on any aspect of this Tip, please feel free to ask.
- The actual Function which does the dirty work:
[CODE=vb]Public Function fStripNonPrinta bleCharacters(s trStringToStrip As String) As String
'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
Dim objExcel As Excel.Applicati on
Set objExcel = CreateObject("E xcel.Applicatio n")
fStripNonPrinta bleCharacters = objExcel.Applic ation.Clean(str StringToStrip)
End Function[/CODE] - The Function call and logic for proving its success:
[CODE=vb]Dim strStringWithNo nPrintables As String, strCleanedStrin g As String
Dim intCharCounter As Integer, strTempUnclean As String, strTempClean As String, strProveClean As String
'Create a String with 5 Non-Printable Characters contained in it
strStringWithNo nPrintables = Chr(7) & "ne" & Chr(13) & "a" & Chr$(10) & "to" & Chr(9) & "!" & Chr(5)
'Code Segment not required, used only to demonstrate the presence of Non-Printable characters in String
strTempUnclean = "|"
For intCharCounter = 1 To Len(strStringWi thNonPrintables )
'Build a String containing the ASCII Codes for all characters within the String as well as the character itself delimited by a '|'. Remember Non-Printable Characters have ASCII Codes between 0 and 31 and in this case will be in positions 1, 4, 6, 9, and 11.
strTempUnclean = strTempUnclean & Asc(Mid$(strStr ingWithNonPrint ables, intCharCounter, 1)) & _
"-" & Mid$(strStringW ithNonPrintable s, intCharCounter, 1) & "|"
Next
'Dump this String to the Immediate Window to see the Non-Printable Characters represented by ASCII Codes 7, 13, 10, 9, and 5. The Characters will also be represented. We'll ompare this afterwards, when this String is run through Excel's Clean() Function
Debug.Print strTempUnclean
'Run the String with Non-Printable Characters through the Clean() Function, print out the result, then prove it worked by using the same logic that was used on the original String.
strTempClean = fStripNonPrinta bleCharacters(s trStringWithNon Printables)
Debug.Print strTempClean '==> Prints neato!
'Code Segment not required, used only to verify that Non-Printable characters were removed from String
strProveClean = "|"
For intCharCounter = 1 To Len strTempClean)
strProveClean = strProveClean & Asc(Mid$(strTem pClean, intCharCounter, 1)) & _
"-" & Mid$(strTempCle an, intCharCounter, 1) & "|"
Next
Debug.Print strProveClean [/CODE] - String before running through Clean() Function (ASCII Codes & Characters):
[CODE=text]
|7-|110-n|101-e|13- from Line #19
|97-a|10-
|116-t|111-o|9- |33-!|5-|[/CODE] - Output of String after running through Clean() Function (Characters/ASCII Codes/Characters)
[CODE=text]neato! from Line #24
|110-n|101-e|97-a|116-t|111-o|33-!| from Line #33[/CODE]
Comment