Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As: change all control characters to an underscore
Hi all! Once again I turn to this wonderful forum for help... I'm busy working on a 'Save As:' macro (using a button) that selects values from certain cells and then use these vlaues as the file name in which the file will be saved. This macro will be displaying a userform with option bottons. The option buttons will display the various recommended file names of which the end-user will have to select one. The problem is that from time to time the end-user will need to use control characters in the applicable cells which determine the file name. I need a macro that will automatically change all control characters to an underscore ("_") within the file name (when hitting the "save" button / control I have created). However, the macro I need must NOT replace the control characters in the actual cells themselves. As I understand control characters are the following: / \ ? % * : | " < . So obviously the macro I need must change all these characters to underscores. SOS...SOS...SOS...SOS...SOS :) -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95648 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As: change all control characters to an underscore
I think that the following will do what you want. Test it carefully with your
actual data. Sub Test() Dim strSource As String Dim strChrsToReplace As String Dim intLgthReplace As Integer Dim i As Integer strSource = Range("A1") 'Note:Chr(34) concatenated in string for double quote. strChrsToReplace = "/ \ ? % * : |" & Chr(34) & "< ." 'Remove any spaces in the string of characters to replace. strChrsToReplace = Replace(strChrsToReplace, Chr(32), "") intLgthReplace = Len(strChrsToReplace) For i = 1 To intLgthReplace If InStr(strSource, _ Mid(strChrsToReplace, i, 1)) 0 Then strSource = Replace(strSource, _ Mid(strChrsToReplace, i, 1), "_") End If Next i Range("A2") = strSource 'Used to test result -- Regards, OssieMac "ARbitOUR" wrote: Hi all! Once again I turn to this wonderful forum for help... I'm busy working on a 'Save As:' macro (using a button) that selects values from certain cells and then use these vlaues as the file name in which the file will be saved. This macro will be displaying a userform with option bottons. The option buttons will display the various recommended file names of which the end-user will have to select one. The problem is that from time to time the end-user will need to use control characters in the applicable cells which determine the file name. I need a macro that will automatically change all control characters to an underscore ("_") within the file name (when hitting the "save" button / control I have created). However, the macro I need must NOT replace the control characters in the actual cells themselves. As I understand control characters are the following: / \ ? % * : | " < . So obviously the macro I need must change all these characters to underscores. SOS...SOS...SOS...SOS...SOS :) -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95648 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the default weight of the underscore? | Excel Discussion (Misc queries) | |||
Viewing Control Characters | Excel Discussion (Misc queries) | |||
How can I display control characters in Excel 2007? | Excel Discussion (Misc queries) | |||
underscore in email address covered by hyperlink underscore | Excel Discussion (Misc queries) | |||
Deleting unwated control characters at the end in a particular column | Excel Programming |