Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 08-25-2010, 01:51 PM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,796
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Function Format Excel Tab Name - remove characters from any string

This function can be used to remove un-wanted characters from any string. Sometime in Excel object model reporting, the user is asked to name the tab.
This function allows the user to type anything, then just removes the characters that could cause a problem.
People can adjust it for just about any purpose.
Code:
'--------------------------------------------------------------------------------
' Procedure : FormatExcelTabName
' DateTime  : 08/05/2010
' Author    : Rx
' Purpose   : Excel - allow user to enter tab name during custom report in Excel
' Add your own characters to avoid in the Case statement after line 40 - can be used for
'     any type of string entry to remove unwanted characters
'--------------------------------------------------------------------------------
' Immediate Window Test:   ? FormatTabName("MySt/r?ing*")                Returns: MyString
Public Function FormatExcelTabName(strExcelTabName As String) As String
          Dim strNewExcelTabName    As String
          Dim intTabLen        As Integer
          Dim intPos           As Integer
10        On Error GoTo PROC_ERROR
20        intTabLen = Len(strExcelTabName)
30        For intPos = 1 To intTabLen
40            Select Case Mid(strExcelTabName, intPos, 1)
                  Case ":", "\", "/", "?", "*", "[", "]"
50                    strNewExcelTabName = strNewExcelTabName & ""
60                Case Else
70                    strNewExcelTabName = strNewExcelTabName & Mid(strExcelTabName, intPos, 1)
80            End Select
90        Next intPos
100       strNewExcelTabName = Trim(strNewExcelTabName)
110       FormatExcelTabName = Trim(Mid(strNewExcelTabName, 1, 31))
PROC_EXIT:
120       On Error Resume Next
130       Exit Function
PROC_ERROR:
140       Select Case Err.Number
                  'Case ###
              Case Else
150               ' add logging code here (optional)
160               Resume PROC_EXIT
170       End Select
End Function

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
 

Tags
excel , function , remove characters , string , tab

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[ACCESS 97] Search multiple keywords bodylojohn Forms 58 03-26-2012 06:19 PM
FTP download to Access sumdumgai Modules & VBA 4 04-02-2010 03:12 AM
D Functions Endre Sample Databases 3 01-15-2010 08:55 AM
Splitted MDB created problem with FindAsYouType Gilfdz Modules & VBA 1 11-13-2007 03:14 AM
remove unwanted characters from a string. 90405 General 2 08-04-2004 07:54 AM




All times are GMT -8. The time now is 10:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World