How to Use Excel Functions in Access

[ADezii]

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:

1. 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.
2. Create an Instance of Excel and assign it to an Object Variable.
3. 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.
4. Set the return value of the Excel Function to the return value of the Access Function.
5. 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.

Careful thought went into the selection of an Excel Function to use for this Tip. I wanted to use a Function that would accept a single Argument for simplicity, it had to have no equivalent counterpart in Access, and it had to have a practical application. I decided to use Excel's Clean() Function which accepts a single String Argument and removes all Non-Printable characters from the String. The Non-Printable characters that I am referring to are ASCII Control Characters with ASCII numbers ranging from 0 to 31. These characters are low level computer codes and are typically used to control some Peripheral Devices such as Printers. Within this range (0 - 31) are such familiar fellows as Line Feeds, Form Feeds, Carriage Returns, Escape Character, Start and End of Text markers, Acknowledge, etc.

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.

1. The actual Function which does the dirty work:
   

   Public Function fStripNonPrintableCharacters(strStringToStrip As String) As String
    
   'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
   Dim objExcel As Excel.Application
    
   Set objExcel = CreateObject("Excel.Application")
    
   fStripNonPrintableCharacters = objExcel.Application.Clean(strStringToStrip)
    
   End Function

2. The Function call and logic for proving its success:
   

   Dim strStringWithNonPrintables As String, strCleanedString 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
   strStringWithNonPrintables = 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(strStringWithNonPrintables)                                               
    
   '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$(strStringWithNonPrintables, intCharCounter, 1)) & _ 
                      "-" & Mid$(strStringWithNonPrintables, 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 = fStripNonPrintableCharacters(strStringWithNonPrintables)
    
   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$(strTempClean, intCharCounter, 1)) & _
                  "-" & Mid$(strTempClean, intCharCounter, 1) & "|"                                      
   Next                                                                                                                
   Debug.Print strProveClean                                                                      

3. String before running through Clean() Function (ASCII Codes & Characters):
   
   

    
   |7-|110-n|101-e|13-        from Line #19
   |97-a|10-
   |116-t|111-o|9- |33-!|5-|

4. Output of String after running through Clean() Function (Characters/ASCII Codes/Characters)
   
   

   neato!        from Line #24
   |110-n|101-e|97-a|116-t|111-o|33-!|        from Line #33

NOTE: Don't let all the code in Item #2 fool you, the only lines needed are actually 3 and 22. All the other lines of code generate the Strings consisting of the ASCII Codes and Characters both before and after the Function call. They can be elimnated.

[FishVal]

Public Function fStripNonPrintableCharacters(strStringToStrip As String) As String
 
'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
Dim objExcel As Excel.Application
 
Set objExcel = CreateObject("Excel.Application")
 
fStripNonPrintableCharacters = objExcel.Application.Clean(strStringToStrip)
 
End Function

The weak side of fStripNonPrinta bleCharacters function is that it is extremely slow as soon as it creates Excel.Applicati on process when called and terminates it on exit when private object variable goes out of scope.
Query based in this function will be veeeeery slow.

On the other hand call like

strResult = Excel.Application.Clean("......")
 

will create static Excel.Applicati on process and all subsequent calls will use it without load time charge. The process stays in memory until Access is closed. Its not very good, but better than query returning 2 records/sec. :) Maximum, disc format cures everything.

P.S. Good tip anyway.

[ADezii]

The weak side of fStripNonPrinta bleCharacters function is that it is extremely slow as soon as it creates Excel.Applicati on process when called and terminates it on exit when private object variable goes out of scope.
Query based in this function will be veeeeery slow.

On the other hand call like

strResult = Excel.Application.Clean("......")
 

will create static Excel.Applicati on process and all subsequent calls will use it without load time charge. The process stays in memory until Access is closed. Its not very good, but better than query returning 2 records/sec. :) Maximum, disc format cures everything.

P.S. Good tip anyway.

Excellant point, FishVal, thanks for making me aware of this shortcoming. My intent, which I obviously should have stated, was for a 1 time/single use only for demonstration purposes only. In this scenario there would have been no performance penalties, but in all reality, this functionality would be used multiple times, probably involving Text File Imports. Again, thank you for your input.

BTW, objExcel could be declared Publically in a Standard Code Module as opposed to a Static Declaration within the Procedure for multiple use of the Function. I really not sure which approach would have the greater overhead. If I were a guessing man, I'd have to go with the Static Declaration as being the costlier. What's your opinion?

P.S. - I'm sure glad that someone actually reads these Tips. (LOL).

[FishVal]

BTW, objExcel could be declared Publically in a Standard Code Module as opposed to a Static Declaration within the Procedure for multiple use of the Function. I really not sure which approach would have the greater overhead. If I were a guessing man, I'd have to go with the Static Declaration as being the costlier. What's your opinion?

Actually I don't now what is better:
1) to declare global object variable on startup or before query runs
2) to let Access automatically create it.
Anyway I would go with the last option as soon as the first doesn't provide any additional safety for the cost of additional efforts.

Just like an infant joke.
Q. Who is better? King or emperor?
A. Both 'em worse.

P.S. - I'm sure glad that someone actually reads these Tips. (LOL).

I read each. :)

Regards,
Fish

[puppydogbuddy]

P.S. - I'm sure glad that someone actually reads these Tips. (LOL).

ADezii,
More people read this stuff than you think! Excellent tip/demo. Do have a tip index that would enable me or another interested party to go back and read previous tips of interest?

Also here are additional references on this topic if anyone is interested.

http://support.microsoft.com/kb/198571
http://www.fabalou.com/Access/Queri...l_functions.asp

http://www.cpearson.com/excel/ATP.htm
an add-in for scientific and engineering calculations

[ADezii]

ADezii,
More people read this stuff than you think! Excellent tip/demo. Do have a tip index that would enable me or another interested party to go back and read previous tips of interest?

Also here are additional references on this topic if anyone is interested.

http://support.microsoft.com/kb/198571
http://www.fabalou.com/Access/Queri...l_functions.asp

http://www.cpearson.com/excel/ATP.htm
an add-in for scientific and engineering calculations

1. Articles
2. Access
3. Full List of Articles and Code in this Section
4. Tips on VBA and Access Programming
   http://www.thescripts.com/forum/thread632608.html