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

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



"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

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

--
Gary''s Student - gsnu200780


"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

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





"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200780


"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

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

--

Dave Peterson

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" wrote:

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

--

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 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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson

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










"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson

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



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
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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson