replace special character for filename save

[anduare2]

Some of my venname data contains an "&" in them. When one is selected from a
dropdown list, it populates cell B2. When the macro runs to save the file I
need to replace the & with the text "and" so my file save routine will not
bomb. But if there is no & in the name I need it to continue on to the save.
It seems like a simple if:then:if:endif would fix it, but I just can't get a
handle on the dim/set value items to get it to replace / what / replacement
code to work for just one instance. Maybe I am looking at it all backwards
again? Here is a list of the macro/code I have pieced together


'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname & "_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub


If possible please include as much detail in example, I am slightly macro
literate but vb ignorant.

Much Appreciated

Martin

 

[Joel]

venname = replace(Worksheets("Sales").Range("B2"),"&","")
vennumber = replace(Worksheets("Sales").Range("B3"),"&","")
venyear = replace(Worksheets("Data").Range("J15"),"&","")
venperiod = replace(Worksheets("Data").Range("J16"),"&","")

 

[Gary''s Student]

Here is an example of how to "cleanup" venname:

Sub anduare()
ch = Chr(38)
speltAnd = "and"
venname = "Rain " & ch & " Snow"
MsgBox (venname)
If InStr(venname, ch) = 0 Then
Else
venname = Replace(venname, ch, speltAnd)
End If
MsgBox (venname)
End Sub

 

[Dave Peterson]

Maybe it's not the "&" that's causing the problem.

I can save a file using a name with an ampersand in it.

(If you're using a Mac, maybe it's different?)

 

[anduare2]

Joel and Gary,
Both of those work beautifully and do exaclty what I thought I needed, but I
have just realized that there are other special char's showing up in the
vendor name list. So far I have found an "*" and and "\". Is there a way
that I can replace multiple special characters with either of these
solutions. Replace & with "and" and/or replace the others with a space " "
when one of the others is found??? I guess I may have to assume at some
point I will have a name with both the & and other(s) special character in
the same name. Now that kind of complicates things abit. (at least for me
it does)

Sorry for the mistake on my part.

Joels your solution was so simple I got it right away.
Gary, you just want to make me think didn't you? I had to run thru it to
see how it worked. I think I understand it thanks to you includeing the
message boxes. I was able to incorporate both versions into my macro/code
and they worked great till I found the other characters.

any more suggestions are greatly appreciated
Thanks again

 

[anduare2]

I get a 1004 error message whenever I run it and the vendor name has either
an & or an / or an *in the name. I thought those were big No No's for
filenames? This is intel/windows xp/vista workstations on a MS server
2000/2003. I can get names with the "-" to save. There may be others that
make it bomb so I just want to blanket kill the special characters and maybe
replace them with either a space or underscore. Any other ideas??? I would
agree with you if it only bombed on one particular vendor name, but I have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

 

[Dave Peterson]

Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list of invalid characters pop up in a yellow bubble.

\/:*?"<>|

I'd add the []'s, too.

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = "test*/*:name.xls"
myChars = "\/:*?""<>|[]"

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
Next iCtr

myStr = Application.Trim(myStr)

MsgBox myStr
End Sub

But there are other things that can cause the save to fail, too.

 

[anduare2]

Dave,

The way you sent it, worked with all the vendor names with those special
chars except the ones with the & sign in them. Once I added the & sign to
the list of chars to replace with a space they are now saving to the
sharepoint server docs library as well. I don't have an explanation for it,
I guess I must have something somewhere formatted or defined strange. I run
office 2003 sp3, I am not certain what the sharepoint server version is, it
is a MS winserver.

I rem'd out your code and redirected to my hard drive and the file does save
to my local drive with the "&" sign in the name. And the 1004 error I get
with the * sign is very specific, scolding me not to use those special
characters in my filename. So I can only presume it is the server or
sharepoint that does not like the & sign. The 1004 error has no addtional
info when it stops with the & problem, it is just blank.

Really, Really appreciate you walking me thru this. Now to figure out how
to disable the Toosl/Macro menu choice, but that is probably a new post.

Thanks again, Much appreciated.

Martin

Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list of invalid characters pop up in a yellow bubble.

\/:*?"<>|

I'd add the []'s, too.

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = "test*/*:name.xls"
myChars = "\/:*?""<>|[]"

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
Next iCtr

myStr = Application.Trim(myStr)

MsgBox myStr
End Sub

But there are other things that can cause the save to fail, too.

I get a 1004 error message whenever I run it and the vendor name has either
an & or an / or an *in the name. I thought those were big No No's for
filenames? This is intel/windows xp/vista workstations on a MS server
2000/2003. I can get names with the "-" to save. There may be others that
make it bomb so I just want to blanket kill the special characters and maybe
replace them with either a space or underscore. Any other ideas??? I would
agree with you if it only bombed on one particular vendor name, but I have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

 

[Dave Peterson]

I don't use sharepoint either. But it sure sounds like it doesn't like it!

Dave,

The way you sent it, worked with all the vendor names with those special
chars except the ones with the & sign in them. Once I added the & sign to
the list of chars to replace with a space they are now saving to the
sharepoint server docs library as well. I don't have an explanation for it,
I guess I must have something somewhere formatted or defined strange. I run
office 2003 sp3, I am not certain what the sharepoint server version is, it
is a MS winserver.

I rem'd out your code and redirected to my hard drive and the file does save
to my local drive with the "&" sign in the name. And the 1004 error I get
with the * sign is very specific, scolding me not to use those special
characters in my filename. So I can only presume it is the server or
sharepoint that does not like the & sign. The 1004 error has no addtional
info when it stops with the & problem, it is just blank.

Really, Really appreciate you walking me thru this. Now to figure out how
to disable the Toosl/Macro menu choice, but that is probably a new post.

Thanks again, Much appreciated.

Martin

Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list of invalid characters pop up in a yellow bubble.

\/:*?"<>|

I'd add the []'s, too.

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = "test*/*:name.xls"
myChars = "\/:*?""<>|[]"

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
Next iCtr

myStr = Application.Trim(myStr)

MsgBox myStr
End Sub

But there are other things that can cause the save to fail, too.

I get a 1004 error message whenever I run it and the vendor name has either
an & or an / or an *in the name. I thought those were big No No's for
filenames? This is intel/windows xp/vista workstations on a MS server
2000/2003. I can get names with the "-" to save. There may be others that
make it bomb so I just want to blanket kill the special characters and maybe
replace them with either a space or underscore. Any other ideas??? I would
agree with you if it only bombed on one particular vendor name, but I have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

:

Maybe it's not the "&" that's causing the problem.

I can save a file using a name with an ampersand in it.

(If you're using a Mac, maybe it's different?)

anduare2 wrote:

Some of my venname data contains an "&" in them. When one is selected from a
dropdown list, it populates cell B2. When the macro runs to save the file I
need to replace the & with the text "and" so my file save routine will not
bomb. But if there is no & in the name I need it to continue on to the save.
It seems like a simple if:then:if:endif would fix it, but I just can't get a
handle on the dim/set value items to get it to replace / what / replacement
code to work for just one instance. Maybe I am looking at it all backwards
again? Here is a list of the macro/code I have pieced together

'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname & "_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub

If possible please include as much detail in example, I am slightly macro
literate but vb ignorant.

Much Appreciated

Martin

 

[Rick Rothstein \(MVP - VB\)]

I don't use SharePoint either, however this link says & are not allowed....

http://support.microsoft.com/default.aspx?scid=kb;en-us;905231

Rick

I don't use sharepoint either. But it sure sounds like it doesn't like it!

Dave,

The way you sent it, worked with all the vendor names with those special
chars except the ones with the & sign in them. Once I added the & sign
to
the list of chars to replace with a space they are now saving to the
sharepoint server docs library as well. I don't have an explanation for
it,
I guess I must have something somewhere formatted or defined strange. I
run
office 2003 sp3, I am not certain what the sharepoint server version is,
it
is a MS winserver.

I rem'd out your code and redirected to my hard drive and the file does
save
to my local drive with the "&" sign in the name. And the 1004 error I
get
with the * sign is very specific, scolding me not to use those special
characters in my filename. So I can only presume it is the server or
sharepoint that does not like the & sign. The 1004 error has no
addtional
info when it stops with the & problem, it is just blank.

Really, Really appreciate you walking me thru this. Now to figure out
how
to disable the Toosl/Macro menu choice, but that is probably a new post.

Thanks again, Much appreciated.

Martin

Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list of invalid characters pop up in a yellow bubble.

\/:*?"<>|

I'd add the []'s, too.

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = "test*/*:name.xls"
myChars = "\/:*?""<>|[]"

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
Next iCtr

myStr = Application.Trim(myStr)

MsgBox myStr
End Sub

But there are other things that can cause the save to fail, too.



anduare2 wrote:

I get a 1004 error message whenever I run it and the vendor name has
either
an & or an / or an *in the name. I thought those were big No No's
for
filenames? This is intel/windows xp/vista workstations on a MS
server
2000/2003. I can get names with the "-" to save. There may be
others that
make it bomb so I just want to blanket kill the special characters
and maybe
replace them with either a space or underscore. Any other ideas???
I would
agree with you if it only bombed on one particular vendor name, but I
have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

:

Maybe it's not the "&" that's causing the problem.

I can save a file using a name with an ampersand in it.

(If you're using a Mac, maybe it's different?)

anduare2 wrote:

Some of my venname data contains an "&" in them. When one is
selected from a
dropdown list, it populates cell B2. When the macro runs to save
the file I
need to replace the & with the text "and" so my file save routine
will not
bomb. But if there is no & in the name I need it to continue on
to the save.
It seems like a simple if:then:if:endif would fix it, but I just
can't get a
handle on the dim/set value items to get it to replace / what /
replacement
code to work for just one instance. Maybe I am looking at it all
backwards
again? Here is a list of the macro/code I have pieced together

'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname &
"_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub

If possible please include as much detail in example, I am
slightly macro
literate but vb ignorant.

Much Appreciated

Martin

 

[anduare2]

Thanks for that link, explains alot. I was talking to our admin that setup
sharepoint and he thought maybe because it uses SQL, it might have
restrictions on certain characters. But the link you supplied cleared up all
questions in regards to that.

Really appreciate that bit of info, Thanks.

Martin

I don't use SharePoint either, however this link says & are not allowed....

http://support.microsoft.com/default.aspx?scid=kb;en-us;905231

Rick

I don't use sharepoint either. But it sure sounds like it doesn't like it!

Dave,

The way you sent it, worked with all the vendor names with those special
chars except the ones with the & sign in them. Once I added the & sign
to
the list of chars to replace with a space they are now saving to the
sharepoint server docs library as well. I don't have an explanation for
it,
I guess I must have something somewhere formatted or defined strange. I
run
office 2003 sp3, I am not certain what the sharepoint server version is,
it
is a MS winserver.

I rem'd out your code and redirected to my hard drive and the file does
save
to my local drive with the "&" sign in the name. And the 1004 error I
get
with the * sign is very specific, scolding me not to use those special
characters in my filename. So I can only presume it is the server or
sharepoint that does not like the & sign. The 1004 error has no
addtional
info when it stops with the & problem, it is just blank.

Really, Really appreciate you walking me thru this. Now to figure out
how
to disable the Toosl/Macro menu choice, but that is probably a new post.

Thanks again, Much appreciated.

Martin

:

Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list of invalid characters pop up in a yellow bubble.

\/:*?"<>|

I'd add the []'s, too.

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = "test*/*:name.xls"
myChars = "\/:*?""<>|[]"

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
Next iCtr

myStr = Application.Trim(myStr)

MsgBox myStr
End Sub

But there are other things that can cause the save to fail, too.



anduare2 wrote:

I get a 1004 error message whenever I run it and the vendor name has
either
an & or an / or an *in the name. I thought those were big No No's
for
filenames? This is intel/windows xp/vista workstations on a MS
server
2000/2003. I can get names with the "-" to save. There may be
others that
make it bomb so I just want to blanket kill the special characters
and maybe
replace them with either a space or underscore. Any other ideas???
I would
agree with you if it only bombed on one particular vendor name, but I
have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

:

Maybe it's not the "&" that's causing the problem.

I can save a file using a name with an ampersand in it.

(If you're using a Mac, maybe it's different?)

anduare2 wrote:

Some of my venname data contains an "&" in them. When one is
selected from a
dropdown list, it populates cell B2. When the macro runs to save
the file I
need to replace the & with the text "and" so my file save routine
will not
bomb. But if there is no & in the name I need it to continue on
to the save.
It seems like a simple if:then:if:endif would fix it, but I just
can't get a
handle on the dim/set value items to get it to replace / what /
replacement
code to work for just one instance. Maybe I am looking at it all
backwards
again? Here is a list of the macro/code I have pieced together

'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname &
"_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub

If possible please include as much detail in example, I am
slightly macro
literate but vb ignorant.

Much Appreciated

Martin

 

[JimL]

Thanks for that link, explains alot.  I was talking to our admin that setupsharepointand he thought maybe because it uses SQL, it might have
restrictions on certain characters.  But the link you supplied cleared up all
questions in regards to that.

Really appreciate that bit of info, Thanks.

Martin

:

I don't useSharePointeither, however this link says & are not allowed.....

http://support.microsoft.com/default.aspx?scid=kb;en-us;905231

Rick

I don't usesharepointeither.  But it sure sounds like it doesn't likeit!
anduare2 wrote:
Dave,
The way you sent it, worked with all the vendor names with those special
chars except the ones with the & sign in them.  Once I added the & sign
to
the list of chars to replace with a space they are now saving to the
sharepointserver docs library as well.  I don't have an explanation for
it,
I guess I must have something somewhere formatted or defined strange. I
run
office  2003 sp3, I am not certain what thesharepointserver versionis,
it
is a MS winserver.
I rem'd out your code and redirected to my hard drive and the file does
save
to my local drive with the "&" sign in the name.  And the 1004 error I
get
with the * sign is very specific, scolding me not to use those special
characters in myfilename.  So I can only presume it is the server or
sharepointthat does not like the & sign.  The 1004 error has no
addtional
info when it stops with the & problem, it is just blank.
Really, Really appreciate you walking me thru this.  Now to figure out
how
to disable the Toosl/Macro menu choice, but that is probably a new post.
Thanks again, Much appreciated.
Martin
:
Open Windows Explorer
Create a new text document
Try renaming it to asdf*asdf.txt
You should see a list ofinvalidcharacters pop up in a yellow bubble..
\/:*?"<>|
I'd add the []'s, too.
Option Explicit
Sub Testme()
    Dim myStr As String
    Dim myChars As String
    Dim iCtr As Long
    myStr = "test*/*:name.xls"
    myChars = "\/:*?""<>|[]"
    For iCtr = 1 To Len(myChars)
        'replace with a space character???
        myStr = Replace(myStr, Mid(myChars, iCtr, 1), " ")
    Next iCtr
    myStr = Application.Trim(myStr)
    MsgBox myStr
End Sub
But there are other things that can cause the save to fail, too.
anduare2 wrote:
I get a 1004 error message whenever I run it and the vendor name has
either
an & or an /  or an *in the name.  I thought those were big No No's
for
filenames?  This is intel/windows xp/vista workstations on a MS
server
2000/2003.  I can get names with the "-" to save.  There may be
others that
make it bomb so I just want to blanket kill the special characters
and maybe
replace them with either a space or underscore.  Any other ideas???
I would
agree with you if it only bombed on one particular vendor name, but I
have
found more.  And it does work correctly for "normal" vendor names.
Appreciate the help
:
Maybe it's not the "&" that's causing the problem.
I can save a file using a name with an ampersand in it.
(If you're using a Mac, maybe it's different?)
anduare2 wrote:
Some of my venname data contains an "&" in them.  When one is
selected from a
dropdown list, it populates cell B2.  When the macro runs to save
the file I
need to replace the & with the text "and" so my file save routine
will not
bomb.  But if there is no & in the name I need it to continue on
to the save.
 It seems like a simple if:then:if:endif would fix it, but I just
can't get a
handle on the dim/set value items to get it to replace / what/
replacement
code to work  for just one instance. Maybe I am looking at it all
backwards
again?   Here is a list of the macro/code I have pieced together
'Procedure to save the Sales Workbook to theSharepointLibrary
Sub SaveWork()
    Dim venname As String
    Dim vennumber As String
    Dim venyear As String
    Dim venperiod As String
    venname = Worksheets("Sales").Range("B2")
    vennumber = Worksheets("Sales").Range("B3")
    venyear = Worksheets("Data").Range("J15")
    venperiod = Worksheets("Data").Range("J16")
    ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname &
"_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
    MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub
If possible please include as much detail in example, I am
slightly macro
literate but vb ignorant.
Much Appreciated
Martin

- Show quoted text -

Please take a look at my commercial program "SharePrep". Its current
version allows a full scan for invalid characters without requiring
registration. However, registration is required for the automated
conversion of characters in the file and folder names.

The link is - http://www.purgeie.com/shareprep

Jim L.